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
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