Home Graphic Excel VBA
Reply: 1

Graphic Excel VBA

Maxime Personnaz
1#
Maxime Personnaz Published in 2018-01-12 23:27:17Z

the following code allow me to plot a graph with the data i selected from tw odifferents colum

definition variables

Dim DerniereLigne_B As Integer
Dim DerniereLigne_C As Integer

'Définition dernière case non vide'
DerniereLigne_B = Range("B65536").End(xlUp).Row
DerniereLigne_C = Range("C65536").End(xlUp).Row

'Creation graph
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Autoclave cycle"""

ActiveChart.SeriesCollection(1).XValues = "='feuillet 1'!$B$4:$B$" & DerniereLigne_B

ActiveChart.SeriesCollection(1).XValues = "='feuillet 1'!$C$4:$C$" & DerniereLigne_C

But i want to make it work what ever sheet i have open. To do so, I create a string variable that is name from my actif sheet

Dim nomfeuille As String
nomfeuille = ActiveSheet.Name

I want to replace my sheet name by my variable created above

ActiveChart.SeriesCollection(1).XValues = _
     "='feuillet 1'!$B$4:$B$" & DerniereLigne_B

I tried it like this

ActiveChart.SeriesCollection(1).XValues = _
      "='nomfeuille'!$B$4:$B$" & DerniereLigne_B

But it is not working. How can i write it right to make it work ?

if it can not work do you have idees to make it work. i want to create a graph from the same column variable everytime but with the active sheet.

Tim Williams
2#
Tim Williams Reply to 2018-01-13 00:02:01Z

Like this:

Dim nomfeuille As String

nomfeuille = ActiveSheet.Name

ActiveChart.SeriesCollection(1).XValues = _
            "='" & nomfeuille & "'!$B$4:$B$" & DerniereLigne_B
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302977 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO