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:=xlDatabase,
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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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:=xlDatabase,
> 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
>

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

Paul

--
Paul at preeve dot plus dot com
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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:=xlDatabase,
>> 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
>>
>
>

....and it worked fine. Thanks again

--
Paul at preeve dot plus dot com
"Paul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> thanks Tom, I'll try that in the office tomorrow
>
> Paul
>
> --
> Paul at preeve dot plus dot com
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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:=xlDatabase,
>>> 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
>>>
>>
>>
>
>