Loop through multiple sheets and create Graph in each sheet

[dilshod_k]

Hi everyone.

I've got workbook with multiple sheets with data. The aim is to build graph on each sheet. I’ve recorded macros which creates the same graph on each sheet (using data from sheet “Control”) of the workbook, but I failed to adjust code in order to create graph for each sheet separately. My understanding is that the following line has to be modified:


ActiveChart.SetSourceData Source:=Range("Control!$R$1:$X$120")

I would be grateful if someone would help with changing "Control" to the name of the active sheet.
I would also appreciate if you would show how to define position and dimensions of the graph. on the sheet.

Thanks in advance,

Dilshod.


The complete code as it follows below:

Sub Grah()

Dim ws As Worksheet



With Application

.ScreenUpdating = False

.EnableEvents = False

End With



For Each ws In Sheets



If ws.Name <> "Control" And ws.Name <> "Response" Then



ws.Activate

Debug.Print ws.Name



Range("B1:B120").Copy Range("R1")

Range("F1:F120").Copy Range("S1")

Range("I1:I120").Copy

Range("T1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("J1:J120").Copy

Range("U1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("M1:M120").Copy

Range("V1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("N1:N120").Copy

Range("W1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns("W:W").EntireColumn.AutoFit

Range("O1:O120").Copy

Range("X1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns("X:X").EntireColumn.AutoFit

Range("S2").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.NumberFormat = "$#,##0.00"

Range("R1").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select

ActiveChart.SetSourceData Source:=Range("Sheetname!$R$1:$X$120")

Range("A1").Select



ActiveSheet.Columns.AutoFit



End If

Next ws

End Sub

 

[Micron]

Try
Dim strSource As String

strSource = "" & ActiveSheet.Name & "!$R$1:$X$120"
ActiveChart.SetSourceData Source:=Range(strSource)

You're turning off screen updating and events but not turning them back on?
IMO, if you disable application settings you should use an error handler lest they remain off.

 

[dilshod_k]

Try
Dim strSource As String

strSource = "" & ActiveSheet.Name & "!$R$1:$X$120"
ActiveChart.SetSourceData Source:=Range(strSource)

You're turning off screen updating and events but not turning them back on?
IMO, if you disable application settings you should use an error handler lest they remain off.

Hi Micron,

While I was waiting for help, by trial and error found solution by changing line to ActiveChart.SetSourceData Source:=ActiveSheet.Range("R1:X120") and it seems it works so far.
Thanks anyway for you help and time. Appreciate it.

 

[Micron]

Your way is better, I think.