• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine two table via VBA & create pivot from array

Chihiro

Excel Ninja
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
 

Attachments

  • Sample Pivot from Array.xlsb
    274.8 KB · Views: 29
It's because you used code without changing below section.
Code:
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.
 
Back
Top