Using a macro to create a pivot table

[john.campbell]

Hi

I have recorded a macro to create a pivot table. The problem is that the data contains a different number of rows each time the macro is run, and if there are more rows than were in the data when the macro was recorded, the pivot table does not include these rows.

This problem is similar to one resolved through the thread below, which helped me to fill down a formula in data with different numbers of rows by amending the code for the macro.

http://www.mrexcel.com/forum/showthread.php?t=5168

Can anyone help?

Thanks

 

[JamesW]

Hi,

This may help:

   lRow = Range("A" & rows.count).End(xlUp).Row
    lColumn = Range("A1").End(xlToRight).Column
    topRow = Range("A" & lRow).End(xlUp).Row
    
     
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Sheet1!R" & topRow & "C1:R" & lRow & "C" & lColumn).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

 

[john.campbell]

Hi James

Thanks very much. I have to confess I am still about of a novice with VB so I wonder if you or someone else could have another look at this. I have created a very basic macro for a pivot table to use as a test. When I have recorded the macro, the VB looks like this:

Sub test() 

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"Sheet1!R1C1:R30C4").CreatePivotTable TableDestination:="", TableName:= _ 
"PivotTable3", DefaultVersion:=xlPivotTableVersion10 
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
ActiveSheet.Cells(3, 1).Select 
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Scheme Type" 
ActiveSheet.PivotTables("PivotTable3").PivotFields("Balance Amount"). _ 
Orientation = xlDataField 
End Sub

I then tried to add in your code to this macro, but I get an error message when I try to run it. This is my effort at integrating your code:

Sub test() 

lRow = Range("A" & rows.count).End(xlUp).Row 
lColumn = Range("A1").End(xlToRight).Column
topRow = Range("A" & lRow).End(xlUp).Row 


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"'Sheet1!R" & topRow & "C1:R" & lRow & "C" & lColumn).CreatePivotTable TableDestination:="", TableName:= _ 
"PivotTable3", DefaultVersion:=xlPivotTableVersion10 
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
ActiveSheet.Cells(3, 1).Select 
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Scheme Type" 
ActiveSheet.PivotTables("PivotTable3").PivotFields("Balance Amount"). _ 
Orientation = xlDataField 
End Sub

Basically I have added in the three lines for lRow, lColumn and topRow. I have then replaced the SourceData from the original code with the SourceData from the code you suggested.

Can you explain where I have gone wrong?

Thanks

John

PS I have found a workaround by referring to "Sheet1!R1C1:R60000" in the original code, but this is a bit rubbish!