How to range table "t" from ActiveSheet "Charts"?

[vangog]

Let's suppose that in sheet "t" where a table is located... In sheet "Charts" I have charts. I have selected the Sheet "Charts" and now I need to access the table in Charts, iterate through and set the styles in charts in ActiveSheet ("Charts").

So I have written this code (intended for old Excel).


Dim wsS As Worksheet, it As ChartObject, table As Range, c As Integer, l As Integer

Set wsS = Sheets("t")
Set table = wsS.Range("A1", Range("D11"))
For l = 1 To table.Rows.Count
For Each it In ActiveSheet.ChartObjects
it.Chart.SeriesCollection(l).Fill.ForeColor = table.Rows(l).Columns.Cells(1).Value
it.Chart.SeriesCollection(l).MarkerStyle = table.Rows(l).Columns.Cells(2).Value
it.Chart.SeriesCollection(l).MarkerSize = table.Rows(l).Columns.Cells(3).Value
it.Chart.SeriesCollection(l).MarkerForegroundColor = table.Rows(l).Columns.Cells(4).Value
Next it
Next



Now I have error 1004 Method Range of obj. Worksheet failed.

How to correct the range method?

[Aussiebear]

You haven't set the active sheet to Charts, so its trying to find chart objects on the wrong sheet.

[vangog]

You haven't set the active sheet to Charts, so its trying to find chart objects on the wrong sheet.
Ah, OK. Thank you. How can I do that? ActiveSheet = "G7" ... error object doesnt support this property or method. Also when I have few Sheets of Charts and I'd click, I dont know the name of the sheet. How to get the name of the selected Sheet?

[vangog]

Solved

Dim wsS As Worksheet, it As ChartObject, table As Range, c As Integer, l As Integer, ChartSheetName As String
ChartSheetName = ActiveSheet.Name
Set wsS = Sheets("t") ' Here is source table with Chart styles values
Sheets("t").Activate
Set table = wsS.Range("A1", Range("D11"))
Sheets(ChartSheetName).Activate