Mannington
New Member
- Joined
- Feb 2, 2011
- Messages
- 8
I have a workbook, where I have named the sheet using a cell reference, eg 032013. I have then created a pivot table on a seperate sheet. Each month a new sheet is created, with the new sheet name, eg. 042013, 052013. I want to change the data source to the new sheet name each month. I seem to be having problems with the sheet name reference, the code below works, but if I try to give it a sheet name based on cell reference it does not work. Any help would be appreciated.
sub update
Sheets("Summary").Select ' contains the pivot table
Dim ptMyPivotTable as PivotTable
Set ptMyPivotTable = ActiveSheet.PivotTables("PivotTable2")
ptMyPivotTable.SourceData= Sheets("032013").Range("a1").CurrentRegion.Address(true, True, xlR1C1, True)
ptMyPivotTable.PivotCache.Refresh
end sub
sub update
Sheets("Summary").Select ' contains the pivot table
Dim ptMyPivotTable as PivotTable
Set ptMyPivotTable = ActiveSheet.PivotTables("PivotTable2")
ptMyPivotTable.SourceData= Sheets("032013").Range("a1").CurrentRegion.Address(true, True, xlR1C1, True)
ptMyPivotTable.PivotCache.Refresh
end sub