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?
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

Board Regular
Joined
Jan 19, 2015
Messages
67
But I am getting error at

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

adr12345

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

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,527
Latest member
pro2go

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top