Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
Hello:
I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
Just questions...
First, are you sure you're on the correct sheet? Second, do you have a pivottable that's named pivottable2? You can rightclick on a pivottable and choose table options to see the name. And third, do you have a field named "sum of amount" on the pivottable named pivottable2? murkaboris wrote: Hello: I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
Hi Dave:
Thank you for responding. Answers: 1) yes I'm on the correct sheet 2) yes I have a pivot table named "pivottable2" -- that's what it named it when I copy-pasted the 1st pivot table into this slot (named it "2") 3) so after I copy-pasted the pivottable1 which became pivottable2 it was exactly the same but instead of regular numbers as displayed in the data field in pivottable1 I needed it displayed as % of row, which in pivot table you can achieve in the layout wizard by clickin on your "data field" in this case the "sum of amount" and selecting under the "Show data as:" the option "% of column". Thank you. Monika "Dave Peterson" wrote: Just questions... First, are you sure you're on the correct sheet? Second, do you have a pivottable that's named pivottable2? You can rightclick on a pivottable and choose table options to see the name. And third, do you have a field named "sum of amount" on the pivottable named pivottable2? murkaboris wrote: Hello: I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
And you verified that the name of that pivottable was still pivottable2, right?
(Maybe you copied|pasted multiple times...) That said, I only see those three problems that can happen on this line: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") I'm hoping that you didn't verify the name--because I'm out of ideas. Sorry. ps. If you test with: With ActiveSheet.PivotTables(1).PivotFields("Sum of Amount") and then With ActiveSheet.PivotTables(2).PivotFields("Sum of Amount") Do either fail? If yes, then I'm sure it's a naming problem. If no, then I'm still out of guesses... murkaboris wrote: Hi Dave: Thank you for responding. Answers: 1) yes I'm on the correct sheet 2) yes I have a pivot table named "pivottable2" -- that's what it named it when I copy-pasted the 1st pivot table into this slot (named it "2") 3) so after I copy-pasted the pivottable1 which became pivottable2 it was exactly the same but instead of regular numbers as displayed in the data field in pivottable1 I needed it displayed as % of row, which in pivot table you can achieve in the layout wizard by clickin on your "data field" in this case the "sum of amount" and selecting under the "Show data as:" the option "% of column". Thank you. Monika "Dave Peterson" wrote: Just questions... First, are you sure you're on the correct sheet? Second, do you have a pivottable that's named pivottable2? You can rightclick on a pivottable and choose table options to see the name. And third, do you have a field named "sum of amount" on the pivottable named pivottable2? murkaboris wrote: Hello: I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
Dave:
I double checked the names and its correct. I copied the pivot once and pasted it next to it....do you think its the issue with the "% of column", it shouldn't --- just not sure. Any general recommendation you can give me when I want to save some memory by using the same source data for pivot so I normally would copy - paste the same pivot next to each other and change the fields where I need to by going to the layout wizard....am I doing it right when I'm recording or should I do something else....? thank you. Monika "Dave Peterson" wrote: And you verified that the name of that pivottable was still pivottable2, right? (Maybe you copied|pasted multiple times...) That said, I only see those three problems that can happen on this line: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") I'm hoping that you didn't verify the name--because I'm out of ideas. Sorry. ps. If you test with: With ActiveSheet.PivotTables(1).PivotFields("Sum of Amount") and then With ActiveSheet.PivotTables(2).PivotFields("Sum of Amount") Do either fail? If yes, then I'm sure it's a naming problem. If no, then I'm still out of guesses... murkaboris wrote: Hi Dave: Thank you for responding. Answers: 1) yes I'm on the correct sheet 2) yes I have a pivot table named "pivottable2" -- that's what it named it when I copy-pasted the 1st pivot table into this slot (named it "2") 3) so after I copy-pasted the pivottable1 which became pivottable2 it was exactly the same but instead of regular numbers as displayed in the data field in pivottable1 I needed it displayed as % of row, which in pivot table you can achieve in the layout wizard by clickin on your "data field" in this case the "sum of amount" and selecting under the "Show data as:" the option "% of column". Thank you. Monika "Dave Peterson" wrote: Just questions... First, are you sure you're on the correct sheet? Second, do you have a pivottable that's named pivottable2? You can rightclick on a pivottable and choose table options to see the name. And third, do you have a field named "sum of amount" on the pivottable named pivottable2? murkaboris wrote: Hello: I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run-time error '1004'
I tried a very small test and it worked fine for me.
I'm not sure what the problem is, so I can't offer any meaningful suggestions. Sorry. murkaboris wrote: Dave: I double checked the names and its correct. I copied the pivot once and pasted it next to it....do you think its the issue with the "% of column", it shouldn't --- just not sure. Any general recommendation you can give me when I want to save some memory by using the same source data for pivot so I normally would copy - paste the same pivot next to each other and change the fields where I need to by going to the layout wizard....am I doing it right when I'm recording or should I do something else....? thank you. Monika "Dave Peterson" wrote: And you verified that the name of that pivottable was still pivottable2, right? (Maybe you copied|pasted multiple times...) That said, I only see those three problems that can happen on this line: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") I'm hoping that you didn't verify the name--because I'm out of ideas. Sorry. ps. If you test with: With ActiveSheet.PivotTables(1).PivotFields("Sum of Amount") and then With ActiveSheet.PivotTables(2).PivotFields("Sum of Amount") Do either fail? If yes, then I'm sure it's a naming problem. If no, then I'm still out of guesses... murkaboris wrote: Hi Dave: Thank you for responding. Answers: 1) yes I'm on the correct sheet 2) yes I have a pivot table named "pivottable2" -- that's what it named it when I copy-pasted the 1st pivot table into this slot (named it "2") 3) so after I copy-pasted the pivottable1 which became pivottable2 it was exactly the same but instead of regular numbers as displayed in the data field in pivottable1 I needed it displayed as % of row, which in pivot table you can achieve in the layout wizard by clickin on your "data field" in this case the "sum of amount" and selecting under the "Show data as:" the option "% of column". Thank you. Monika "Dave Peterson" wrote: Just questions... First, are you sure you're on the correct sheet? Second, do you have a pivottable that's named pivottable2? You can rightclick on a pivottable and choose table options to see the name. And third, do you have a field named "sum of amount" on the pivottable named pivottable2? murkaboris wrote: Hello: I've recorded a macro in which I'm copy-pasting the same pivot table in the same worksheet and then changing the display of number inside to show as % of column but am receiving: Run-time error '1004' - Unable to get the PivotTables property of the Worksheet class The error highlights the following portion of the macro: With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") Here is portion of the macro including the line above that is highlighted when I click on "Debug": Range("A4:G14").Select Range("G14").Activate Selection.Copy Range("J4").Select ActiveSheet.Paste Columns("J:J").EntireColumn.AutoFit Columns("K:P").Select Selection.ColumnWidth = 8.8 Range("K8:P14").Select Application.CutCopyMode = False With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Amount") .Calculation = xlPercentOfColumn .NumberFormat = "0.00%" End With Please help, I'm not too good with macros :). Thank you. Monika -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error: '1004' | Excel Worksheet Functions | |||
Run-time error '1004' | Excel Discussion (Misc queries) | |||
run time error 1004 | Excel Worksheet Functions | |||
Run time error 1004 | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel |