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("A4:P4"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Whe=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 ("A4:P4") 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:P 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!

Putting this code ahead of the first line of your Sub should solve problems
#1 and #2 -

As for #3 - once you've created one of your graphs, use Tools | Macro |
Record New Macro to record the keystrokes you use to set up things the way
that you want regarding Value Data Labels, their alignment, etc. You can use
the recorded macro code as a model for adding to your own Sub to finish it
off.

Sub TestCreatingSheet()
Dim NewSheetName As String
Dim DataRange As String

'make sure sheet name < 31 characters
NewSheetName = "Gráfica Curva_S_" & _
Format(Now(), "m_d_yy_hh_mm")
On Error Resume Next
'go to worksheet to place chart on
Worksheets(NewSheetName).Select
If Err < 0 Then
'sheet DID NOT exist
'add it
Sheets.Add
' new sheet is now the ActiveSheet
'give it the name you want
ActiveSheet.Name = NewSheetName
Err.Clear
Else
'sheet already exists
'may want to exit or
'warn user?
End If
On Error GoTo 0
'You can now use NewSheetName
'to create your chart on
'now back to your sheet with data
Worksheets("Gráfica Curva_S").Select
Range("A4").Select
'now to get dynamic source data
'must have empty cell to mark
'end of data in column P at row 4
DataRange = "A4:" & Range("A4").End(xlToRight).Address
'now where you used .Range("A4:P4") you can use
' .Range(DataRange)
'just so you can see results
MsgBox "DataRange is " & DataRange
End Sub

Hope this helps you out at least a little.

"Ed" wrote:

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("A4:P4"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Whe=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 ("A4:P4") 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:P 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!

Hello JLatham! hey, thanks again for your help! Yeah the code you sent me
definetely helped me make some usefull changes, I seem to understand now a
bit more about codes.
thanks!,
Ed



"JLatham" wrote:

Putting this code ahead of the first line of your Sub should solve problems
#1 and #2 -

As for #3 - once you've created one of your graphs, use Tools | Macro |
Record New Macro to record the keystrokes you use to set up things the way
that you want regarding Value Data Labels, their alignment, etc. You can use
the recorded macro code as a model for adding to your own Sub to finish it
off.

Sub TestCreatingSheet()
Dim NewSheetName As String
Dim DataRange As String

'make sure sheet name < 31 characters
NewSheetName = "Gráfica Curva_S_" & _
Format(Now(), "m_d_yy_hh_mm")
On Error Resume Next
'go to worksheet to place chart on
Worksheets(NewSheetName).Select
If Err < 0 Then
'sheet DID NOT exist
'add it
Sheets.Add
' new sheet is now the ActiveSheet
'give it the name you want
ActiveSheet.Name = NewSheetName
Err.Clear
Else
'sheet already exists
'may want to exit or
'warn user?
End If
On Error GoTo 0
'You can now use NewSheetName
'to create your chart on
'now back to your sheet with data
Worksheets("Gráfica Curva_S").Select
Range("A4").Select
'now to get dynamic source data
'must have empty cell to mark
'end of data in column P at row 4
DataRange = "A4:" & Range("A4").End(xlToRight).Address
'now where you used .Range("A4:P4") you can use
' .Range(DataRange)
'just so you can see results
MsgBox "DataRange is " & DataRange
End Sub

Hope this helps you out at least a little.

"Ed" wrote:

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("A4:P4"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Whe=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 ("A4:P4") 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:P 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!

Glad it did you some good.

"Ed" wrote:

Hello JLatham! hey, thanks again for your help! Yeah the code you sent me
definetely helped me make some usefull changes, I seem to understand now a
bit more about codes.
thanks!,
Ed



"JLatham" wrote:

Putting this code ahead of the first line of your Sub should solve problems
#1 and #2 -

As for #3 - once you've created one of your graphs, use Tools | Macro |
Record New Macro to record the keystrokes you use to set up things the way
that you want regarding Value Data Labels, their alignment, etc. You can use
the recorded macro code as a model for adding to your own Sub to finish it
off.

Sub TestCreatingSheet()
Dim NewSheetName As String
Dim DataRange As String

'make sure sheet name < 31 characters
NewSheetName = "Gráfica Curva_S_" & _
Format(Now(), "m_d_yy_hh_mm")
On Error Resume Next
'go to worksheet to place chart on
Worksheets(NewSheetName).Select
If Err < 0 Then
'sheet DID NOT exist
'add it
Sheets.Add
' new sheet is now the ActiveSheet
'give it the name you want
ActiveSheet.Name = NewSheetName
Err.Clear
Else
'sheet already exists
'may want to exit or
'warn user?
End If
On Error GoTo 0
'You can now use NewSheetName
'to create your chart on
'now back to your sheet with data
Worksheets("Gráfica Curva_S").Select
Range("A4").Select
'now to get dynamic source data
'must have empty cell to mark
'end of data in column P at row 4
DataRange = "A4:" & Range("A4").End(xlToRight).Address
'now where you used .Range("A4:P4") you can use
' .Range(DataRange)
'just so you can see results
MsgBox "DataRange is " & DataRange
End Sub

Hope this helps you out at least a little.

"Ed" wrote:

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("A4:P4"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Curva-S"""
ActiveChart.Location Whe=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 ("A4:P4") 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:P 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!