Macro & pivot table problem

[JRS]

Hi Everyone,

I am struggling to get a macro working to create a pivot table and need some help…

I have a worksheet with 2 buttons one is “collect data” and the other is “Generate report”

I have managed to get “collect data” to ask for a date and then it retrieves ‘an amount’ of data based on that date from another file. This amount of data varies each time.
I then recorded a macro for the “Generate report” button which selects this data (I have it select all the data whether there is loads or just a small amount) and then it creates the required pivot table and chart on a new sheet.

When I first open the file it all works fine, then I collect a second lot of data which works fine, but when I try to put this second lot through the pivot table macro, it always stops working. I think it’s a problem with creating a new sheet to put the pivot table on as the debug option always takes me to this line:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")

Plus it works perfectly if I close it down and restart the file. I just need it to work continuously without the need to close it down. Ideally it would keep opening new sheets to place the tables and charts on.

If anyone can help out I would really appreciate it as this has been coursing me problems for a while.
For reference, the full “generate report” macro code is shown below, Many thanks.




Sub GenerateReport()
'
' Generation Macro
'
ActiveSheet.Range("A1:C1", ActiveSheet.Range("A1:C1").End(xlDown)).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Qual Pareto'!R6C1:R29C3").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Fault"

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlDataField
.NumberFormat = "£#,##0.00"
End With

Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")
ActiveChart.Location Where:=xlLocationAsNewSheet
Application.CommandBars("PivotTable").Visible = False
Sheets("Chart1").Select

End Sub

 

[mrMozambique]

Is it possible for you to name the range first and keep that named range throughout the process? Also, why is your range only A3?

 

[JRS]

I'm not sure why the range is A3, thats just what appeared when I recorded the original macro.
It seems to work every time for the first time I run the macro, its just when I try to it the second time that it fails.

Do you know what that range of A3 referring to?



...As an extra clue....
When I manually make a second pivot table (not using the macro) the only difference in the VBA code is that on the line:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")

it says "sheet2" rather than "sheet1"
I tried to get this to increment each time the macro runs but still cant get it to work.

Please help anyone!

 

[JRS]

I think it is a range problem...

The debug error message says "subscript out of range"

Anyone know how I fix this please?

 

[JRS]

For some reason (which I cannot figure out)
If I completely delete the line:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")

Then the chart seems to work fine every time, I'm not even sure what it did in the first place, it was put there by the recorded macro, can anyone tell me what it was supposed to do?

Thanks