urgent help required!!

[adr12345]

Hi,

I am getting type mismatch error while running this code.


Dim table2 As PivotTable
Worksheets("CR").Select
Set data = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Range("A1").End(xlToRight).End(xlDown)))
Set table2 = data.CreatePivotTable(Worksheets("SUMMARY").Range("B23"))

I am using set data command to run earlier part of code also. and that time it is working fine.

I am using same variable again in above mentioned code and it is not working.

can I use the same variable again or do i need to declare new variable?

 

[VBA Geek]

try like this


Set Data = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Range("A1").End(xlToRight).End(xlDown)).Address(, , xlR1C1))
Set table2 = Data.CreatePivotTable(Worksheets("SUMMARY").Range("B23").Address(, , xlR1C1))

 

[adr12345]

I was able to do this in first part of the code. second time this command is not working. any idea why it is not working?
Also i am trying to avoid using R1C1 reference

 

[VBA Geek]

try Set table2 = data.CreatePivotTable(Worksheets("SUMMARY").Range("B23").Address(, , xlR1C1, True))

this will not force you to work with R1C1 style, it's just for the code to know where to add the pivot table, the TableDestination argument is a string and not a range object

 

[adr12345]

But I am getting error at

Set data = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Range("A1").End(xlToRight).End(xlDown)))

 

[VBA Geek]

But I am getting error at

Set data = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Range("A1").End(xlToRight).End(xlDown)))

that's not the code I posted...

 

[adr12345]

I used

Set data1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, range("A1", range("A1").End(xlToRight).End(xlDown)).Address(, , xlR1C1))
Set Table3 = data1.CreatePivotTable(Worksheets("SUMMARY").range("B35"))


Its working fine.. However It did not accept Address(, , xlR1C1)) for set table command. Hence I removed it.