[VBA] Creating a multi-sheet pivot table, using a macro

Introduction

We will see the use of "Dictionary" object (keys), in a two-dimensional array (variable).

The basic workbook

Is a workbook regroupind the sales per month, sellers and products sold.
In this workbook, 12 sheets, one for each month.
In each of these sheets, three columns:
- Column A: the names of the sellers,
- Column B: the names of products sold,
- Column C: the amount.

VBA code

To integrate it into your workbook, copy the entire code below, ALT + F11, Insert/Module, paste the code. To use it, close the Visual Basic Editor to return to your workbook, then press ALT + F8, select "RécapAvecSommeDesColonnesC" then click "Run."
Adapt:
- The name of the récap sheet
- The Columns: A, B and C

Option Explicit  

Sub RécapAvecSommeDesColonnesC()  
Dim Feuille As Worksheet, i As Long  
Dim TablVendeurs(), DicoVendeurs As Object  
Dim TablVentes(), DicoVentes As Object  
Dim Sommes()  

Set DicoVendeurs = CreateObject("Scripting.Dictionary")  
Set DicoVentes = CreateObject("Scripting.Dictionary")  

'*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES*******  

'remplissage des étiquettes de lignes et de colonnes sans doublons  
For Each Feuille In ThisWorkbook.Worksheets  
    If Feuille.Name <> "Récap" Then  
        With Feuille  
            TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp))  
            For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1)  
                If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1)  
            Next i  
            TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp))  
            For i = LBound(TablVentes, 1) To UBound(TablVentes, 1)  
                If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1)  
            Next i  
        End With  
    End If  
Next Feuille  
'remplissage de la variable tableau 2D grâce aux clés de Dictionary  
ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count)  
For Each Feuille In ThisWorkbook.Worksheets  
    If Feuille.Name <> "Récap" Then  
        With Feuille  
            For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row  
                Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value  
            Next i  
        End With  
    End If  
Next Feuille  

'*******RESTITUTION DES DONNEES*******  

With Sheets("Récap")  
    .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys)  
    .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys  
    .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes()  
End With  
End Sub 

Download Link

You can download a working example, here

See also

Knowledge communities.

• Forum
• Excel forum
• Office software forum

This document entitled « VBA - Creating a multi-sheet pivot table, using a macro » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.