D
David J
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:=xlDatabase, SourceData:=
WorkSheet("WSName").Range("C10:C14").CreatePivotTable(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
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:=xlDatabase, SourceData:=
WorkSheet("WSName").Range("C10:C14").CreatePivotTable(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