I have been struggling with a problem in Excel and hopefully someone can
help me. I manage an Excel-file which is used to manage or sales guys and their their efforts in the markets. I want to create a bulit in report into the file. A dynamic report which can be updated by the user. The user fills in customer names (one per line) and properties into the file. I have marked this area and made it into a pivot table. In a separate report sheet I have
a few graphs. The source data for these graphs can be found in the pivot report. I have sorted the pivot tables (I have three different report tables) so that the customer with the biggest potential is at the top. When the user fills in a new customer which has very much potential in USD and the pivot tables are refreshed the new customer will be shown at the top of the tables and subsequently the graphs in the report sheet will be updated and show the new customer. I have recorded a macro (using Excels recorder) that executes the refreshing of the Pivot tables.
I have added a push button to the report sheet, and the push button is linked to the macro. So, when the user pushes the button the graphs in the report sheet are updated. This works very well. The problem occurs when I want to hide the Pivot tables. Obviously I don't want to have visible pivot tables in the user version. The previously described macro does not work when the Pivot is hidden. I tried to record a new macro according to the following logic; First I hide the Pivot table sheet. Then I place the cursor in the report sheet. I then select record macro. The first thing that I do while recording is unhiding the Pivot
table sheet, then I place the cursor in the Pivot table and click the refresh
button in the toolbar. Then I hide the Pivot sheet and go back to the report sheet and stop recording. This macro does not work, the following message comes up; Run-time error '1004'; Unable to get the PivotTables property of the Worksheet class
When I press debug the following piece of VBA code is shown:
Sub TESTI2()
'
' TESTI2 Macro
' Macro recorded 19.3.2009 by jukka-pekka niemi'
'
Range("B21").Select
Sheets("Target pivot3level").Visible = True
Range("A6").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveWindow.SelectedSheets.Visible = False
Range("B13").Select
End Sub
VBA enters a yellow arrow at the line
"ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh" inicating that the problem lies here?
The first and last commands are simply from moving the cursor to the report sheet.
Do you have any idea how I could fix this. I'm out of ideas. I have tried to
have three different Pivot tables in different sheets but that doesn't help.
I would very appreciate if you could help me out.
Best regards,
Juex
help me. I manage an Excel-file which is used to manage or sales guys and their their efforts in the markets. I want to create a bulit in report into the file. A dynamic report which can be updated by the user. The user fills in customer names (one per line) and properties into the file. I have marked this area and made it into a pivot table. In a separate report sheet I have
a few graphs. The source data for these graphs can be found in the pivot report. I have sorted the pivot tables (I have three different report tables) so that the customer with the biggest potential is at the top. When the user fills in a new customer which has very much potential in USD and the pivot tables are refreshed the new customer will be shown at the top of the tables and subsequently the graphs in the report sheet will be updated and show the new customer. I have recorded a macro (using Excels recorder) that executes the refreshing of the Pivot tables.
I have added a push button to the report sheet, and the push button is linked to the macro. So, when the user pushes the button the graphs in the report sheet are updated. This works very well. The problem occurs when I want to hide the Pivot tables. Obviously I don't want to have visible pivot tables in the user version. The previously described macro does not work when the Pivot is hidden. I tried to record a new macro according to the following logic; First I hide the Pivot table sheet. Then I place the cursor in the report sheet. I then select record macro. The first thing that I do while recording is unhiding the Pivot
table sheet, then I place the cursor in the Pivot table and click the refresh
button in the toolbar. Then I hide the Pivot sheet and go back to the report sheet and stop recording. This macro does not work, the following message comes up; Run-time error '1004'; Unable to get the PivotTables property of the Worksheet class
When I press debug the following piece of VBA code is shown:
Sub TESTI2()
'
' TESTI2 Macro
' Macro recorded 19.3.2009 by jukka-pekka niemi'
'
Range("B21").Select
Sheets("Target pivot3level").Visible = True
Range("A6").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveWindow.SelectedSheets.Visible = False
Range("B13").Select
End Sub
VBA enters a yellow arrow at the line
"ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh" inicating that the problem lies here?
The first and last commands are simply from moving the cursor to the report sheet.
Do you have any idea how I could fix this. I'm out of ideas. I have tried to
have three different Pivot tables in different sheets but that doesn't help.
I would very appreciate if you could help me out.
Best regards,
Juex