| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time Error 1004: Application-defined or Object-defined Error
Hi, Can anyone help me. I get this message when using the Macro's and I'm not
sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time Error 1004: Application-defined or Object-defined Error
When you recorded this macro the PivotTable was created on a new sheet called
Sheet4. If you delete this sheet and run the macro again the PivotTable is created on a new sheet probably called Sheet5 but definately not called Sheet4. So when you reference Sheet4 in the last statement of your macro it fails. One way to fix this is to name the sheet created for the PivotTable and then use that name in the rest of the macro, something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 Activesheet.Name = "MyPivot" 'rest of code ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Hope this helps Rowan "Adrian" wrote: Hi, Can anyone help me. I get this message when using the Macro's and I'm not sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time Error 1004: Application-defined or Object-defined Err
Hi Rowan,
Thanks for the response but the error still occurs at the same point even if the sheet has been named... Any other Idea's? "Rowan" wrote: When you recorded this macro the PivotTable was created on a new sheet called Sheet4. If you delete this sheet and run the macro again the PivotTable is created on a new sheet probably called Sheet5 but definately not called Sheet4. So when you reference Sheet4 in the last statement of your macro it fails. One way to fix this is to name the sheet created for the PivotTable and then use that name in the rest of the macro, something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 Activesheet.Name = "MyPivot" 'rest of code ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Hope this helps Rowan "Adrian" wrote: Hi, Can anyone help me. I get this message when using the Macro's and I'm not sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time Error 1004: Application-defined or Object-defined Err
Hi Adrian
I should have forseen that. You need to repeat the line: Sheets("Sheet1").Select just before the statement to create the second pivot table (the one causing the error). Also the code: With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With will cause an erorr if there is no blank data so you may want to wrap an errorhandler around it: On Error Resume Next With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With On Error Goto 0 (watch out for the line wrap in the two examples above). Regards Rowan "Adrian" wrote: Hi Rowan, Thanks for the response but the error still occurs at the same point even if the sheet has been named... Any other Idea's? "Rowan" wrote: When you recorded this macro the PivotTable was created on a new sheet called Sheet4. If you delete this sheet and run the macro again the PivotTable is created on a new sheet probably called Sheet5 but definately not called Sheet4. So when you reference Sheet4 in the last statement of your macro it fails. One way to fix this is to name the sheet created for the PivotTable and then use that name in the rest of the macro, something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 Activesheet.Name = "MyPivot" 'rest of code ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Hope this helps Rowan "Adrian" wrote: Hi, Can anyone help me. I get this message when using the Macro's and I'm not sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time Error 1004: Application-defined or Object-defined Err
Hi Rowan,
Still doesn't work... However I think I know why... After the first table is created and the second table is created a message pops up asking if you want to use the same data as the first table to reduce the file size. I believe this is causing the problem because if I click No when it asks you if you want to reduce the file size everything works perfectly. So I guess I may not be able to do exactly what I would like... Thanks for the help... "Rowan" wrote: Hi Adrian I should have forseen that. You need to repeat the line: Sheets("Sheet1").Select just before the statement to create the second pivot table (the one causing the error). Also the code: With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With will cause an erorr if there is no blank data so you may want to wrap an errorhandler around it: On Error Resume Next With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With On Error Goto 0 (watch out for the line wrap in the two examples above). Regards Rowan "Adrian" wrote: Hi Rowan, Thanks for the response but the error still occurs at the same point even if the sheet has been named... Any other Idea's? "Rowan" wrote: When you recorded this macro the PivotTable was created on a new sheet called Sheet4. If you delete this sheet and run the macro again the PivotTable is created on a new sheet probably called Sheet5 but definately not called Sheet4. So when you reference Sheet4 in the last statement of your macro it fails. One way to fix this is to name the sheet created for the PivotTable and then use that name in the rest of the macro, something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 Activesheet.Name = "MyPivot" 'rest of code ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Hope this helps Rowan "Adrian" wrote: Hi, Can anyone help me. I get this message when using the Macro's and I'm not sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time Error 1004: Application-defined or Object-defined Err
Hi Adrian
The final statement in the macro is what you get when you respond yes to the question of if you want to use the same data for the new pivot table so it should work. My modified code which works looks like this: Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase _ , SourceData:="Sheet1!R1C1:R2500C37").CreatePivotTab le _ TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.Name = "MyPivot" ActiveSheet.PivotTableWizard TableDestination:= _ ActiveSheet.Cells(3, 1) ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("StatusDescription").Orientation = xlDataField On Error Resume Next With ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("StatusDescription") .PivotItems("(blank)").Visible = False End With On Error GoTo 0 Sheets("Sheet1").Select ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1"). _ PivotCache.CreatePivotTable TableDestination:="", _ TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10 Regards Rowan "Adrian" wrote: Hi Rowan, Still doesn't work... However I think I know why... After the first table is created and the second table is created a message pops up asking if you want to use the same data as the first table to reduce the file size. I believe this is causing the problem because if I click No when it asks you if you want to reduce the file size everything works perfectly. So I guess I may not be able to do exactly what I would like... Thanks for the help... "Rowan" wrote: Hi Adrian I should have forseen that. You need to repeat the line: Sheets("Sheet1").Select just before the statement to create the second pivot table (the one causing the error). Also the code: With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With will cause an erorr if there is no blank data so you may want to wrap an errorhandler around it: On Error Resume Next With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With On Error Goto 0 (watch out for the line wrap in the two examples above). Regards Rowan "Adrian" wrote: Hi Rowan, Thanks for the response but the error still occurs at the same point even if the sheet has been named... Any other Idea's? "Rowan" wrote: When you recorded this macro the PivotTable was created on a new sheet called Sheet4. If you delete this sheet and run the macro again the PivotTable is created on a new sheet probably called Sheet5 but definately not called Sheet4. So when you reference Sheet4 in the last statement of your macro it fails. One way to fix this is to name the sheet created for the PivotTable and then use that name in the rest of the macro, something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 Activesheet.Name = "MyPivot" 'rest of code ActiveWorkbook.Worksheets("MyPivot").PivotTables(" PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Hope this helps Rowan "Adrian" wrote: Hi, Can anyone help me. I get this message when using the Macro's and I'm not sure how to fix... Below is a snippet of code - The error occurs on the last three lines of code... Any suggestions? Thanks Sheets.Add Sheets.Add Sheets("Sheet1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "StatusDescription" ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription"). _ Orientation = xlDataField With ActiveSheet.PivotTables("PivotTable1").PivotFields ("StatusDescription") .PivotItems("(blank)").Visible = False End With Sheets("Sheet1").Select ActiveWorkbook.Worksheets("Sheet4").PivotTables("P ivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
| "Run Time Error 1004 Application Defined or Object Defined Error." | Excel Programming | |||
| Runtime Error 1004 -- Application Defined or Object Defined Error | Excel Programming | |||
Hybrid Mode
