working with ranges
would refreshing the pivot table
as and when source sheet changes not solve the problem?
--
hemu
"Gary Keramidas" wrote:
i've used somethink like this
dim lastrow as long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R3C2:R" & lastrow & "C18").CreatePivotTable TableDestination:= _
"[" & Curbook & "]Sheet2!R4C2", TableName:="PivotTable2",
DefaultVersion:= _
xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
where lastrow is a variable depending on the number of rows
--
Gary
"veryeavy" wrote in message
...
Hi,
Am suffering from rust and fatigue - am sure this muust be something simple.
In time-honoured fashion I am recording macros and then modifying them. This
is my first attempt at doing this to a Pivot Table. This is what I have
recorded:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Data!R6C1:R611C11").CreatePivotTable TableDestination:= _
"'[FDMFormatTemplate c Instructions.XLS]Country'!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
I want the "Data!R6C1:R611C11" to be dynamic - I have the code to create
this string, but my problem is in passing the variable. Let's say the string
is X - it seems I can't simply say blah blah SourceData: = X blah blah...
(where X="Data!R6C1:R611C11").
I have a sinking feeling this is something blindingly obvious ...
|