Excel VBA: PivotCaches.Create Type Mismatch

Question

I used to work with vb a long time back (as part of the visual studio pkg) back in the day, and a new job has me working a little bit with VBA as part of a side project. I wouldn't say I'm straight of the noobie boat, but I'm still re-learning a lot of what I used to do (not to mention, incorporating excel specific bits that wasn't touched upon when I was learning VB in visual studio)

I've already had several pivot tables done via vba in another projects, but I have a particular project right now that has me stumped.

I've been reusing the code for some of the Pivot tables listed in a VBA Excel reference book, and it has been working fine just far. But in this particular workbook, a button is pressed on the worksheet which calls a function while passing a few variables along. Everything's usually fine and dandy, but as I try to create the Cache, I receive an error telling me that I have a mismatch error? From my understanding, strings and variants can be passed as source data when creating a pivot cache, but having repeated the code below several times before, I've had no issue until now.

---

Private Function createPivotTable(ByRef sheetName As Variant, ByVal sheetNum As Integer)

Dim DataRange As Range
Dim pivotTableCache As PivotCache
Dim pivotTableReport As PivotTable

ActiveWorkbook.Worksheets("EXAMPLE SHEET").Activate
Range("A1").Select
Set DataRange = Selection.CurrentRegion

Worksheets.Add.Name = sheetName

Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _ 
      SourceData:=DataRange)
Set pivotTableReport = pivotTableCache.createPivotTable(TableDestination:=ActiveWorkbook.Worksheets(sheetName).Range("A1"), _
TableName:="PVR")

.... 

So I'm essentially stuck. I've found another thread that sort of danced around a similar problem here. The top most rated comment did solve one issue, in that I no longer has a mismatch when I passed it along the range location as a string, but then as I tried to create the actual table itself, I kept getting a 1004 error telling me that I need to select more than one row of data (from what I could interpret - sadly enough, the msgbox for the error code cuts off the line which is kind of funny haha). But, and I've double checked, the selection does indeed grab my entire table (all 6 columns and 1000 something rows).

Any help or pointing in the right direction would be appreciated.

*To verify; I pass along sheetName as a variant instead of a string because I use an input box asking the user to name the worksheet, and as a fail safe, dimensioning the handle for the inputbox as a variant allows me to check if the user has cancelled the operation (which then returns a value of false instead of a blank string).

Answer 1

It looks like although you have the proper range identified, it isn't finding the data because your range doesn't refer to the sheet that contains the data. You might want to try:

Private Function createPivotTable(ByRef sheetName As Variant, ByVal sheetNum As Integer)

   Dim pivotTableCache As PivotCache
   Dim pivotTableReport As PivotTable

   Worksheets.Add.Name = sheetName

   Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _ 
     SourceData:=Sheets("EXAMPLE SHEET").Range("A1").CurrentRegion)
   Set pivotTableReport = pivotTableCache.createPivotTable( _
     TableDestination:=ActiveWorkbook.Worksheets(sheetName).Range("A1") _
     , TableName:="PVR")

   ....