I am consolidating date every two weeks into this workbook and each time I
will change the worksheet name to match the current period i.e. "Period X".
I have the pivot table on another worksheet and want to automatically change
the source data worksheet name to match the current period or allow a user to
input the current period number. Here is the code I have for doing this, but
I have a syntax error I cannot figure out.
PeriodNumber = InputBox(Prompt:="Enter the Period Number for the Summary
Report", Title:="Summary Report Period")
WSName = "Period " & PeriodNumber

'The error is in this portion of the code

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
WorkSheet("WSName").Range("C10:C14").CreatePivotTa ble(TableDestination:="'Period Report'!R3C1", TableName:="PivotTable2")

ActiveSheet.PivotTables("PivotTable2").RowGrand = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Billing Code", "Client Name", "Project No."), ColumnFields:="Name"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Total")
.Orientation = xlDataField
.Caption = "Sum of Total"
.Function = xlSum
End With
End Sub

Try...

...., sourcedata:=WorkSheet(WSName).range(....

(drop the double quotes)

David J wrote:

I am consolidating date every two weeks into this workbook and each time I
will change the worksheet name to match the current period i.e. "Period X".
I have the pivot table on another worksheet and want to automatically change
the source data worksheet name to match the current period or allow a user to
input the current period number. Here is the code I have for doing this, but
I have a syntax error I cannot figure out.
PeriodNumber = InputBox(Prompt:="Enter the Period Number for the Summary
Report", Title:="Summary Report Period")
WSName = "Period " & PeriodNumber

'The error is in this portion of the code

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
WorkSheet("WSName").Range("C10:C14").CreatePivotTa ble(TableDestination:="'Period Report'!R3C1", TableName:="PivotTable2")

ActiveSheet.PivotTables("PivotTable2").RowGrand = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Billing Code", "Client Name", "Project No."), ColumnFields:="Name"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Total")
.Orientation = xlDataField
.Caption = "Sum of Total"
.Function = xlSum
End With
End Sub

--

Dave Peterson