VBA Error - Pivot Table

[mehc011]

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.