urgent help required!!

adr12345

Board Regular
Joined
Jan 19, 2015
Messages
67
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
But I am getting error at

Set data = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Range("A1").End(xlToRight).End(xlDown)))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top