G
Guest
Hello I have the following code:
Sub Gráfica_Curva_S()
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Curva-S").Range("A44"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Gráfica Curva-S"
With ActiveChart.Axes(xlCategory)
..HasMajorGridlines = True
..HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
..HasMajorGridlines = True
..HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
End Sub
I have 3 questions, help on any is greatly appreciated:
1) On the 6th line of the code, how do I manage to make the range more
dynamic? Instead of having ("A44") to have from A4 until the last column
containing data? (By the way, it is maybe usefull to mention that Row 1
contains the number of that (week) so from A I have on Row 1 from 1:15, so
maybe I can have a MAX formula and the code takes that result to specify the
columns for the range or something)
2) On line 10, I specified the name for the Sheet that will be created when
I run the Macro. But when I already have that sheet created and I run the
macro it gives a Run-time Error because that sheet already exists and it
creates a sheet called Sheet # but what is after line 10 of the code is not
created. How do I manage, again, to make a more flexible name, maybe Curva-S
& "current date" (dd-mm-yy) or something like that?
3) I would like to also automatically generate Value Data Labels, aligned
above, rotated 90°, size 6 and Arial font.
Thanks a lot!
Sub Gráfica_Curva_S()
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Curva-S").Range("A44"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Gráfica Curva-S"
With ActiveChart.Axes(xlCategory)
..HasMajorGridlines = True
..HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
..HasMajorGridlines = True
..HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
End Sub
I have 3 questions, help on any is greatly appreciated:
1) On the 6th line of the code, how do I manage to make the range more
dynamic? Instead of having ("A44") to have from A4 until the last column
containing data? (By the way, it is maybe usefull to mention that Row 1
contains the number of that (week) so from A I have on Row 1 from 1:15, so
maybe I can have a MAX formula and the code takes that result to specify the
columns for the range or something)
2) On line 10, I specified the name for the Sheet that will be created when
I run the Macro. But when I already have that sheet created and I run the
macro it gives a Run-time Error because that sheet already exists and it
creates a sheet called Sheet # but what is after line 10 of the code is not
created. How do I manage, again, to make a more flexible name, maybe Curva-S
& "current date" (dd-mm-yy) or something like that?
3) I would like to also automatically generate Value Data Labels, aligned
above, rotated 90°, size 6 and Arial font.
Thanks a lot!