Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
As the last step in a series of macros, I created three pivot tables to
summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12)
workbooks("Production.xls").PivotCaches.Add(Source Type:=xlDatabase, SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 -- Regards, Tom Ogilvy Mark R wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
You should always try to avoid 'hard coding' ranges in
your macros, as they always (or can) change. Assuming your data and headings are all in contiguous cells, and as you say, it starts in A1, you can use Range("A1").CurrentRegion.Address as the range of cells to build the pivot table over. The CurrentRegion figures out the proper number of rows and columns, but will not give the correct results if you don't have data in contiguous cells. I have found this method to work well. If the name of the workbook is really constant, you can just Activate that workbook (Workbook ("Production.xls").Activate), and then replace the R1C1 stuff with SourceData:= Range("A1).CurrentRegion.Address. Hope this helps Steve -----Original Message----- As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correction - Pivot Table question
You don't need the .address on CurrentRegion. Sorry
-----Original Message----- As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correction AGAIN...not enuf coffee - Pivot Table question
Mine was a little different, so I think you DO need
the .address. You can try it both ways and see what happens..sorry for the confusion -----Original Message----- You don't need the .address on CurrentRegion. Sorry -----Original Message----- As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12)
workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3" Modified to work in Excel 2000, worked fine for me (just removed the DefaultVersion argument which isn't supported in xl2000). Maybe you were a victim of wordwrap in the email. If you need to make the destination variable, you would need set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = worksheets("12-21-2003").Range("B9") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Again, that worked fine for me in Excel 2000 -- Regards, Tom Ogilvy Mark R wrote in message ... My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
Tom,
Appreciate your help. For some reason, it's not working for me. I need to go today, but will try to do some reading and figure out why, and maybe post back when I can characterize the failure better. One thing I may not have been clear about is that the worksheet is not 12-21-2003 - that was just he name of the worksheet when I recorded the original macro. "Tom Ogilvy" wrote in message ... set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3" Modified to work in Excel 2000, worked fine for me (just removed the DefaultVersion argument which isn't supported in xl2000). Maybe you were a victim of wordwrap in the email. If you need to make the destination variable, you would need set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = worksheets("12-21-2003").Range("B9") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Again, that worked fine for me in Excel 2000 -- Regards, Tom Ogilvy Mark R wrote in message ... My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
you want the pivot table and the source to both be on the active sheet?
so the modification would be: set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = ActiveSheet.Range("W2") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Hard to give you something better than working code. -- Regards, Tom Ogilvy Mark R wrote in message ... Tom, Appreciate your help. For some reason, it's not working for me. I need to go today, but will try to do some reading and figure out why, and maybe post back when I can characterize the failure better. One thing I may not have been clear about is that the worksheet is not 12-21-2003 - that was just he name of the worksheet when I recorded the original macro. "Tom Ogilvy" wrote in message ... set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3" Modified to work in Excel 2000, worked fine for me (just removed the DefaultVersion argument which isn't supported in xl2000). Maybe you were a victim of wordwrap in the email. If you need to make the destination variable, you would need set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = worksheets("12-21-2003").Range("B9") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Again, that worked fine for me in Excel 2000 -- Regards, Tom Ogilvy Mark R wrote in message ... My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
Thanks Tom,
I agree, that you are rather generous in writing the code. Perhaps, as you suggest, the problem is email workwrap. I'll try typing in your code, since I'm unclear where, if at all, line breaks would be problematic. I assume I change the value of i to correspond with the pivot table number that is used in the subsequent instructions giving the arrangement of the pivot table. "Tom Ogilvy" wrote in message ... you want the pivot table and the source to both be on the active sheet? so the modification would be: set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = ActiveSheet.Range("W2") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Hard to give you something better than working code. -- Regards, Tom Ogilvy Mark R wrote in message ... Tom, Appreciate your help. For some reason, it's not working for me. I need to go today, but will try to do some reading and figure out why, and maybe post back when I can characterize the failure better. One thing I may not have been clear about is that the worksheet is not 12-21-2003 - that was just he name of the worksheet when I recorded the original macro. "Tom Ogilvy" wrote in message ... set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3" Modified to work in Excel 2000, worked fine for me (just removed the DefaultVersion argument which isn't supported in xl2000). Maybe you were a victim of wordwrap in the email. If you need to make the destination variable, you would need set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = worksheets("12-21-2003").Range("B9") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Again, that worked fine for me in Excel 2000 -- Regards, Tom Ogilvy Mark R wrote in message ... My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table question
Tom,,
Got everything working, including three different pivot tables on the sheet. Your code, unsurprisingly, was perfect. I needed to adjust the pivottable name in the statements that followed which gave the design of the table, and had to change the location of the finished table to an area on my worksheet that was clear. Thanks again. Now I can go back to designing furniture! Mark R "Tom Ogilvy" wrote in message ... you want the pivot table and the source to both be on the active sheet? so the modification would be: set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = ActiveSheet.Range("W2") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Hard to give you something better than working code. -- Regards, Tom Ogilvy Mark R wrote in message ... Tom, Appreciate your help. For some reason, it's not working for me. I need to go today, but will try to do some reading and figure out why, and maybe post back when I can characterize the failure better. One thing I may not have been clear about is that the worksheet is not 12-21-2003 - that was just he name of the worksheet when I recorded the original macro. "Tom Ogilvy" wrote in message ... set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3" Modified to work in Excel 2000, worked fine for me (just removed the DefaultVersion argument which isn't supported in xl2000). Maybe you were a victim of wordwrap in the email. If you need to make the destination variable, you would need set rng = Activesheet.Range("A1").CurrentRegion.Resize(,12) set dest = worksheets("12-21-2003").Range("B9") i = 5 workbooks("Production.xls").PivotCaches. _ Add(SourceType:=xlDatabase,SourceData:= _ rng.Address(1,1,xlR1C1,True)).CreatePivotTable TableDestination:= _ dest, TableName:="PivotTable" & i Again, that worked fine for me in Excel 2000 -- Regards, Tom Ogilvy Mark R wrote in message ... My apologies for not getting it, but neither solution seemed to work. The syntax here is way over my head. Tom, when I copied and pasted the code you sent into the module I get a syntax error. Sclark, when I changed the range as you indicated, I still get the same run time error. I understand the problem of static range, but I recorded the macro to make the pivot table, and that's the code that Excel generated. Writing code for a pivot table from scratch, I fear, is well beyond my abilities. Please note that the references I have trouble are not only the sheet and range of the data for the pivot table (and by the way, the data is always contiguous), but also the sheet name for the location (Tabledestination) of the pivot table. Again, I fear I'm a little dense here, but the code to paste in lieu of the ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 statement may be what I need. This is a one time only project to get production lists for my small woodworking business. Thanks again for you help. "Mark R" wrote in message ... As the last step in a series of macros, I created three pivot tables to summarize my data. The macros were recorded. When I run the macros, I get the error: Run-time error '1004': Application-defined or object-defined error When I go to the debugger, the instruction: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'12-21-2003'!R1C1:R90C12").CreatePivotTable TableDestination:= _ "'[Production.xls]12-21-2003'!R2C23", TableName:="PivotTable3", _ DefaultVersion:=xlPivotTableVersion10 is highlighted. I assume the problem is that when I recorded the macro the active sheet was named "12-21-03", and the data was in the range R1C1:R90C12. However, that is not the name of the sheet now, and the number of rows in the data range will vary. If this is indeed the problem, could someone give me the syntax to change this instruction to refer to the current active sheet and the current active range (it won't necessarliy be R1C1:R90C12) of my data. (the data I want for the pivot table will always start at R1C1, but will end at RxC12 where x is the number or rows, which will be different each time I run this). The name of the workbook will always be Production.xls. If there are other problems besides these references, any hint of where to go to solve them would be appreciated. Again, thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Question | Excel Worksheet Functions | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Programming |