0

I am pretty new to VBA, so please excuse me if this is a simple question! I have created a template report (titled "Client_Name") that I want populated for each client - in essence all this involves is selecting the client in the PivotTable's report filter and copying the PivotTable's data values to the template. In order to automate this, I have created the following code to duplicate the template and rename it for the client currently selected in the PivotTable:

Sheet_Name_To_Create = Sheets("Pivot").Range("B1").Value
Sheets("Client_Name").Select
Sheets("Client_Name").Copy After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = Sheet_Name_To_Create

My next step would be to go back to the PivotTable ("Pivot" worksheet) and copy the data to the newly created client worksheet. However, the worksheet's name will obviously change differ for each client. Is there a way to reference the VBA code to select the worksheet whose name is the same as the client currently shown in the PivotTables's report filter?

Community
  • 1
  • 1
Nat Aes
  • 887
  • 5
  • 18
  • 34

1 Answers1

0

Sheets(ActiveSheet.Name) is an inconvenient way of saying ActiveSheet.

Just capture ActiveSheet in a variable after copying.

Dim CopiedSheet as Worksheet
Set CopiedSheet = ActiveSheet

You don't need sheet's name when you have the sheet itself.

Recommended reading: How to avoid using Select in Excel VBA macros

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks for this tip, although wouldn't I run into a circular error whereby the ActiveSheet is the recently duplicated template, and then switches to the Pivot worksheet when I move to copy the data? So once I have switched to the Pivot sheet, CopiedSheet (i.e. ActiveSheet) is now the Pivot sheet itself and can't be directed to the duplicated template which is no longer the ActiveSheet! – Nat Aes Jan 06 '14 at 18:53
  • @user2696883 `ActiveSheet` is a property provided by Excel. It changes automatically to reflect sheet activation. At any given moment it contains a reference to a sheet. You copy this reference. Your copy won't change automatically. – GSerg Jan 06 '14 at 19:25