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

Hmmm - after all the banging of heads and gnashing of teeth I thought I would
try the "original" code as recorded ... needless to say that doesn't work so
.... drawing board - I'm coming back ...

"veryeavy" wrote:

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

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

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