Combine two table via VBA & create pivot from array

[Chihiro]

This is sample for creating Pivot table from two tables with same dimensions using VBA.

Merging array is based on code I found at StackOverflow, few years back. Unfortunately, I forgot to write down the URL and am unable to find source for it. If anyone know the URL, let me know.

Reference to Microsoft ActiveX Data Objects Recordset x.0 Library is added to the project.

Read link below for additional steps you can use to fill Pivot Table.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotcache-createpivottable-method-excel

 

[Chihiro]

It's because you used code without changing below section.

With rs.Fields
    For col = LBound(data, 2) To UBound(data, 2)
        Select Case col
        Case Is = 5, 7, 11
            .Append data(1, col), adDouble
        Case Else
            .Append data(1, col), adVarChar, 35
        End Select
    Next
End With

Above section specifies data type for each column. Since, you only have 2 numeric type columns (5, 9) you should change "Case Is =" line to match.

Also, don't forget to add reference to Microsoft ActiveX Data Objects Recordest x.x library.