Pivot Table question
Mark,
Thanks for the feedback.
Glad it worked and you were able to adapt it.
good luck with your business.
--
Regards,
Tom Ogilvy
Mark R wrote in message
...
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.
|