Type mismatch when trying to create a pivot table using vba with data range in another worksheet

Question

Hello when trying to create a pivot table with the datasource being set a range within a different worksheet I am constantly getting either a type mismatch or a 1004 error depending on how I am trying to declare my range for the source data. Below is my code does anyone have any thoughts on how to properly set my range so that this method will work or should I use a different method to create the table?

Worksheets.Add.Name = sheetName

rowCount = CStr(Sheets(sourceName).UsedRange.Rows.Count)

Set rng = ThisWorkbook.Worksheets(sourceName).Range("A1:W" & rowCount)



'Create the Pivot Cache and Table

Set objCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=rng)

Set objTable = objCache.CreatePivotTable(TableDestination:=ActiveWorkbook.Worksheets(sourceName).Range("A1"))

Martin Jackson Martin Jackson · Accepted Answer · 2014-08-04T21:02:45

Try putting your whole range in a string:

Worksheets.Add.Name = sheetName

rowCount = Cstr(Sheets(sourceName).UsedRange.Rows.Count)

rowCount = "A1:W" & rowCount

Set rng = ThisWorkbook.Worksheets(sourceName).Range(rowCount)



'Create the Pivot Cache and Table

Set objCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=rng)

Set objTable = objCache.CreatePivotTable(TableDestination:=ActiveWorkbook.Worksheets(sourceName).Range("A1"))

Type mismatch when trying to create a pivot table using vba with data range in another worksheet

1 Answers