Creating Pivot tables by VBA for varying amounts of data
 

Hi all,

I have to create a number of pivot tables from the same block of data, and
repeat this with new data four weeks later etc. Data is always in the range
("A3:V" & x) where x varies each 4 weeks.
I have successfully in the past substituted "selection" for the R1C1 range
created in a recorded macro for

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:=Selection)'

but this is now coming up with a "type mismatch" error.
If anyone has any ideas I would be very grateful. I am using Office XP.

TIA

Paul

--
Paul at preeve dot plus dot com

Creating Pivot tables by VBA for varying amounts of data
 

Dim rng as Range
With Worksheets("Data")
set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup))
End with
set rng = rng.Resize(,22)
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=rng.Address(1,1,xlR1C1,True)

--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
Hi all,

I have to create a number of pivot tables from the same block of data, and
repeat this with new data four weeks later etc. Data is always in the
range ("A3:V" & x) where x varies each 4 weeks.
I have successfully in the past substituted "selection" for the R1C1 range
created in a recorded macro for

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:=Selection)'

but this is now coming up with a "type mismatch" error.
If anyone has any ideas I would be very grateful. I am using Office XP.

TIA

Paul

--
Paul at preeve dot plus dot com

Creating Pivot tables by VBA for varying amounts of data
 

thanks Tom, I'll try that in the office tomorrow

Paul

--
Paul at preeve dot plus dot com
"Tom Ogilvy" wrote in message
...
Dim rng as Range
With Worksheets("Data")
set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup))
End with
set rng = rng.Resize(,22)
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=rng.Address(1,1,xlR1C1,True)

--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
Hi all,

I have to create a number of pivot tables from the same block of data,
and repeat this with new data four weeks later etc. Data is always in the
range ("A3:V" & x) where x varies each 4 weeks.
I have successfully in the past substituted "selection" for the R1C1
range created in a recorded macro for

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:=Selection)'

but this is now coming up with a "type mismatch" error.
If anyone has any ideas I would be very grateful. I am using Office XP.

TIA

Paul

--
Paul at preeve dot plus dot com

Creating Pivot tables by VBA for varying amounts of data
 

....and it worked fine. Thanks again

--
Paul at preeve dot plus dot com
"Paul" wrote in message
...
thanks Tom, I'll try that in the office tomorrow

Paul

--
Paul at preeve dot plus dot com
"Tom Ogilvy" wrote in message
...
Dim rng as Range
With Worksheets("Data")
set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup))
End with
set rng = rng.Resize(,22)
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=rng.Address(1,1,xlR1C1,True)

--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
Hi all,

I have to create a number of pivot tables from the same block of data,
and repeat this with new data four weeks later etc. Data is always in
the range ("A3:V" & x) where x varies each 4 weeks.
I have successfully in the past substituted "selection" for the R1C1
range created in a recorded macro for

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:=Selection)'

but this is now coming up with a "type mismatch" error.
If anyone has any ideas I would be very grateful. I am using Office XP.

TIA

Paul

--
Paul at preeve dot plus dot com