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.
.
|