Referencing previous sheet in macro for excel chart

1
votes

I would like to be able to reference the next worksheet in a workbook using VBA, but avoid referencing the sheet name as I hope to be able to apply the macro to a number of different workbooks.

I am writing an Excel Macro which is for formatting an excel chart. In the chart's series title I would like to make the series name reference a cell in the next worksheet.

I have tried the following which is not working:

ActiveChart.SeriesCollection(1).Name = "='ActiveChart.Next.Select'!$B$1"

However this is not working.

What is the most effective way of getting the series name field of a chart to reference a cell in the following worksheet within a macro?

I have also tried the following, which does not work either:

ActiveChart.SeriesCollection(1).Name = "=Sheets(1)!$B$1"

Please bear in mind that this is a workbook which is structured with a chart, then a sheet, chart, sheet and so on...

2
excelworksheetvba

2 Answers

1
votes

You can use this one-liner

ActiveChart.SeriesCollection(1).Name = "='" & Sheets(ActiveChart.Parent.Parent.Name).Next.Name & "'!B1

  1. Sheets(ActiveChart.Parent.Parent.Name returns the name of the next sheet.
  2. The pair of ' are important to use when referring to sheetnames. i.e. a sheet named My Sheet won't work as My Sheet!B1, it must be 'My Sheet'!B1
0
votes

Looks like you are trying to put VBA code instead of classical Excel formula reference. I havent ever tried it but I pressume that it wont work. If you want to reference something to a cell you must create the formula in that good old clear syntax with =, sheetname, !, cell address something like:

=Sheet1!A1

You can ofcourse use VBA to create this formula for you and assign it to the field. Try this code:

Dim ws As Worksheet
Dim formula As String

Set ws = ActiveSheet.Next
formula = "=" & ws.name & "!A1"

ActiveChart.SeriesCollection(1).name = formula