Unfixing source data range for a pivot table using vba
 

When I create a pivot table for the first time, the cell references are fixed
in R1C1 format. I would like to have a macro that will enable me to create
the same pivot table, and at the same time, update those references to the
range of data that would have changed from time to time. Right now I'm stuck
when trying to change the source data code in the Visual Basic editor.

Currently using MS excel 2003.

Unfixing source data range for a pivot table using vba
 

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:=xlDatab ase, _
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



"freespirited_74" wrote:

When I create a pivot table for the first time, the cell references are fixed
in R1C1 format. I would like to have a macro that will enable me to create
the same pivot table, and at the same time, update those references to the
range of data that would have changed from time to time. Right now I'm stuck
when trying to change the source data code in the Visual Basic editor.

Currently using MS excel 2003.

Unfixing source data range for a pivot table using vba
 

Thanks heaps! I've spent a couple of days trying to find the solution to
this..

"OssieMac" wrote:

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:=xlDatab ase, _
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



"freespirited_74" wrote:

When I create a pivot table for the first time, the cell references are fixed
in R1C1 format. I would like to have a macro that will enable me to create
the same pivot table, and at the same time, update those references to the
range of data that would have changed from time to time. Right now I'm stuck
when trying to change the source data code in the Visual Basic editor.

Currently using MS excel 2003.