VBA: Renaming the active sheet

Question

I've run into an issue that seems to be straight forward, but for whatever reason, is giving me an error message in my code. I'm using a Macro to create a Pivot Table and would like to rename the sheet that is created for the Pivot Table. My only problem is that the active sheet will not rename and error message "Run-time 9: Subscript out of range" is given . The code is below and the error occurs on the last line:

Sub Macro24()

Dim VendorData As String
Dim Lastrow As Long
Dim PivotSheet As String

Sheets("Statement").Select
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Statement").Select
ActiveWorkbook.Names.Add Name:="VendorData", RefersTo:= _
    "=Statement!$A$4:$C$" & Lastrow

Sheets.Add
PivotSheet = ActiveSheet.Name

Which line is producing the Run-time 9 error? This error means that the object you are referring in the code is not available in the workbook. Are you getting that error with the line Sheets("Statement").Select? If so, make sure that the sheet called Statement is available. Otherwise the last two lines of the code you showed are not supposed to produce that error. — Subodh Tiwari sktneer
The very last line PivotSheet = ActiveSheet.Name is where I get the error. It's simply not renaming the active sheet to PivotSheet — user8517443
Also what's the point of getting the activesheet's name into the variable PivotSheet? Are you going to use it later in the code? If you just want to rename the sheet after it is added, just use ActiveSheet.Name="Pivot Sheet" — Subodh Tiwari sktneer
Yes I'm going to use the variable;e later in the code to create the Pivot Table. Unless there is another way that does not involve creating the variable that you know of — user8517443
I cannot digest why would you get the error with the last line. Is the actual code similar what you showed in the description? — Subodh Tiwari sktneer

Scott Craner Scott Craner · Accepted Answer · 2017-10-17T19:04:49

Use worksheet variable:

Dim ws As Worksheet
Set ws = Worksheets.Add
ws.name = "PivotSheet"
pivotsheet = ws.Name

VBA: Renaming the active sheet

1 Answers