Renaming Visible Sheets Only

[WBTKbeezy]

Hi, I am trying to rename only visible charts based on a range from a main
tab. Currently I was attempting to do something like the following:

For i = 1 To Charts.Count
If Charts(i).Visible = True Then
Charts(i).Name = range(indirect("Title" & i)).Value
End If
Next

The named range I want for each chart is housed on one main tab and are
named "Title1", “Title2" etc... and each number in the range name matches up
with the chart number (so for Chart 6 I would want renamed to the value in
range Title6). I see now the indirect function doesn't work, can anyone help
me make this work?

Thanks
K

 

[Jim Thomlinson]

You either need to spcify the sheet that contains the named range or use a
different approact to get to the named range...

For i = 1 To Charts.Count
If Charts(i).Visible = True Then
Charts(i).Name = worksheets("main").range("Title" & i).Value
End If
Next i

or

For i = 1 To Charts.Count
If Charts(i).Visible = True Then
Charts(i).Name = thisworkbook.names("Title" & i).Referestorange.value
End If
Next i

 

[WBTKbeezy]

Thanks Jim, that was exactly what I needed!

You either need to spcify the sheet that contains the named range or use a
different approact to get to the named range...

For i = 1 To Charts.Count
If Charts(i).Visible = True Then
Charts(i).Name = worksheets("main").range("Title" & i).Value
End If
Next i

or

For i = 1 To Charts.Count
If Charts(i).Visible = True Then
Charts(i).Name = thisworkbook.names("Title" & i).Referestorange.value
End If
Next i