Hi,
If I understand your query correctly then the source data for the pivot
table changes and you want a macro to be able to recreate the pivot table
with the new source data range.
The following code clears the old pivot table, selects the source data and
creates the new pivot table.
There are other ways of defining the source data range but I would need to
have more info about the range. The following method works provided there are
no blank cells in the top row or left column.
You might need to edit the macro and replace the cell reference in
Range("A1").Select with the top left cell in your source data. (Include the
column header because it is used in the pivot table)
The pivot table code was recorded and the source data range replaced with
the range variable 'rng1'. Ensure you replace the range including the double
quotes and leave the closing bracket.
Sub Macro4()
Dim rng1 As Range
Sheets("Sheet1").Select
'Delete old pivot table first
'Set following range to include your existing pivot table
'the range to clear can be larger than the pivot table
Range("G1:Q17").Clear
Range("A1").Select 'Top left cell of source. (On column header)
'The following code was recorded by holding the Ctrl and
'Shift and then pressing right arrow and then down arrow.
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'Assign the selected data to a variable
Set rng1 = Selection
'Replace the source data range with the range variable
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=rng1) _
.CreatePivotTable _
TableDestination:="'Sheet1'!R1C8", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
'For some reason in xl2002 the new pivot table is blank until selected
'and hense the following line.
Range("J7").Select
End Sub
Hope you can extract enough info from the example to do what you want. If it
fails, then post the code you have an I'll see if I can help some more.
Regards,
OssieMac
