Referencing a variable in place of worksheet name

G'Day everyone

Trying to create a Pivot on a worksheet that was created for a variable entered by user. But i am having trouble getting Excel to accept the variable as the worksheet name. (below in Red) Any ideas??

    'Create sheet 1
    
    Set NewSheet1 = Worksheets.Add
    NewSheet1.Name = outputvar1
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "This sheet will hold the pivot for:"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = outputvar1
    
            'Creates the pivot table on sheet
                    Dim WSD As Worksheet
                    Dim PTCache As PivotCache
                    Dim PT As PivotTable
                    Dim PRange As Range
                    Dim FinalRow As Long
                    Set WSD = Worksheets(outputvar1)

                    
                    ' Delete any prior pivot tables
                    For Each PT In WSD.PivotTables
                        PT.TableRange2.Clear
                    Next PT
                    
                    ' Define input area and set up a Pivot Cache
                    FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
                    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
                    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
                        
                    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"), TableName:="PivotTable1")
                    PT.ManualUpdate = True
                    ' Set up the row  & column fields
                    PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"
                        
                    ' Set up the data fields
                    With PT.PivotFields("Revenue")
                        .Orientation = xlDataField
                        .Function = xlSum
                        .Position = 1
                    End With
                    
                    ' Calc the pivot table
                    PT.ManualUpdate = False
                    PT.ManualUpdate = True

Could the problem be this?

You have this line of code
NewSheet1.Name = outputvar1

but I think you need this instead

outputvar1 = NewSheet1.Name

Originally Posted by Mallycat

Could the problem be this?

You have this line of code
NewSheet1.Name = outputvar1

but I think you need this instead

outputvar1 = NewSheet1.Name

This is not the problem, "outputvar1" has been given a value earlier in the code.

using "NewSheet1.Name = outputvar1" sets the new sheets name to be that of the value set for "outputvar1"

I can reference the variable "outputvar1" successfully when naming the newly created worksheet and setting the value of cell D1 - But i cannot use it to identify the worksheet in VBA code again. Why is this??

Looks OK to me. I have tested it and it works find.

Try putting a break in the code just before this line, and seeh what the variable evalutes to

'SECTION TWO - create new worksheets for each individual cost centre based on variables

    'Create sheet 1
   
    Set Newsheet1 = Worksheets.Add
    Newsheet1.Name = outputvar1
    Range("A1").FormulaR1C1 = "This sheet will hold the pivot for:"
    Range("D1").FormulaR1C1 = outputvar1

^^ This works fine

'Creates the pivot table on sheet        
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
  "Control!$A:$X").CreatePivotTable TableDestination:= _
  "'[Overheads.Engine.xls]100241'!R5C1", TableName:="PivotTable1",
 DefaultVersion:=xlPivotTableVersion10

^^ so does this (where the sheet is referenced as a fixed name (100241)

'Creates the pivot table on sheet        
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
  "Control!$A:$X").CreatePivotTable TableDestination:= _
  "'[Overheads.Engine.xls]outputvar1'!R5C1", TableName:="PivotTable1",
 DefaultVersion:=xlPivotTableVersion10

BUT it is really important that I am able to reference the sheet as a variable as i am dynamically creating up to 10 sheets which could have any different cost centre number (which means i cant hard code the sheet number as above)

Since i create the pivots on each sheet as i go, is there any way to replace referncing the sheet by it name by using some other type such as "active sheet??"