VBA Error - Pivot Table

Hi

I am trying to generate a pivot table but I keep getting a "type mismatch" error. Code I use is:

Worksheets("O").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set myRng = Selection

Worksheets("Generate").Activate

Set tableRange = Range(ActiveCell, ActiveCell.Offset(0, 0))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=myRng, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=tableRange, TableName:="PivotTable4", DefaultVersion _
:=xlPivotTableVersion12


I have noticed that changing "SourceData:=myRng" to actual range (e.g. SourceData:="O!A1:R1000" works. However, I have been able to generate the pivot table successfully with the above code for several other files. I can't figure out why this one is generating an error.

Welcome to the forum.

Try changing it to myRng.Address


Regards, TMS



Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here