Reference to ActiveSheet Object Outside of VBA

Question

I am looking to run the following tasks:

1: Create a template worksheet with dynamic charts (i.e. the charts are linked to a data range that frequently change in size)

2: The template is copied several times into new worksheets, with each worksheet automatically renamed using code that cycles through the PivotItems in another worksheet.

3: Date from a PivotTable is then copied through to these duplicated worksheet, with the dynamic tables in each worksheet now reflecting the data copied through to it.

I have coded most of these tasks although am encountering the following issue. Dynamic charts are created through reference to named ranges that are defined using the OFFSET and COUNTA functions. Each time the template duplicates, I have set named ranges to be defined in the new worksheet using the following code:

ActiveWorkbook.ActiveSheet.Names.Add Name:="Label", RefersToR1C1:= _
    "=OFFSET(ActiveSheet!R46C3,1,0,COUNTA(ActiveSheet!R46C3:R69C3)-2)"
ActiveWorkbook.ActiveSheet.Names("Label").Comment = ""

ActiveWorkbook.ActiveSheet.Names.Add Name:="YTD2012", RefersToR1C1:= _
    "=OFFSET(ActiveSheet!R46C5,1,0,COUNTA(ActiveSheet!R46C3:R69C3)-2)"
ActiveWorkbook.ActiveSheet.Names("YTD2012").Comment = ""

ActiveWorkbook.ActiveSheet.Names.Add Name:="YTD2013", RefersToR1C1:= _
    "=OFFSET(ActiveSheet!R46C6,1,0,COUNTA(ActiveSheet!R46C3:R69C3)-2)"
ActiveWorkbook.ActiveSheet.Names("YTD2013").Comment = ""

[I am using clustered column charts, hence the requirement for two named ranges relating to data, and one relating to data labels]

The ActiveSheet object is required given that the worksheet names will differ each time I run the report. However, when selecting the data source for the chart on the template I cannot make reference to ActiveSheet given this is a VBA object. I would rather keep the charts pre-formatted on the template (as opposed to create them each time through VBA) given their formatting and positioning. Is there any way to make reference to ActiveSheet or something similar in Excel, or is the only solution to have the charts created for each worksheet in VBA?

Tim Williams Tim Williams · Accepted Answer · 2014-01-09T19:06:38

If you don't include the worksheet name it will default to the activesheet:

ActiveWorkbook.ActiveSheet.Names.Add Name:="Label", RefersToR1C1:= _
    "=OFFSET(R46C3,1,0,COUNTA(R46C3:R69C3)-2)"

If you did want to use the sheet name then it would be ActiveSheet.Name

With ActiveWorkbook.ActiveSheet
    .Names.Add Name:="Label", RefersToR1C1:= _
    Replace("=OFFSET('<nm>'!R46C3,1,0,COUNTA('<nm>'!R46C3:R69C3)-2)","<nm>", .Name)
End With

Need to add single quotes if the sheet names could contain spaces.

Reference to ActiveSheet Object Outside of VBA

1 Answers