CODEWITHSUNDEEP
excelchartsexcel-2003vba
user2271563
user2271563

Reputation: 35

Dynamically changing sheet titles

I have a several .xls files that have their sheet names the same as their filenames. Since the sheet names are updated with each .xls file, the code I currently have does not update dynamically.

Is there a way to update the sheet name dynamically so the code can be used across all of the .xls files?

Range("A3:E4500").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("named").Range("A3:E4500"), _
PlotBy:=xlColumns

where "named" will be dynamically changing. I tried adding this to the code but if gives error messages. WBname gives the correct name for each sheet but the "Source" does not like the string.

Dim WBname As String
WBname = Replace(ActiveWorkbook.Name, ".xls", "")
Application.ScreenUpdating = False
Range("A3:E4500").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("WBname").Range("A3:E4500"), _
PlotBy:=xlColumns

Upvotes: 2

Views: 249

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

WBname is a variable. Inside Double Quotes it becomes a string.

Try this

Change

ActiveChart.SetSourceData Source:=Sheets("WBname").Range("A3:E4500"), _ PlotBy:=xlColumns

to this.

ActiveChart.SetSourceData Source:=Sheets(WBname).Range("A3:E4500"), _ PlotBy:=xlColumns

Upvotes: 2

Related Questions