Excel – VBA script for multiple pivot tables

excelpivot tablevba

I replace source data ever week, and have many pivot tables attached to it. However, when I refresh the tables, they start to miss some data. So, I went ahead and recorded some "macros" to create and replace new pivot tables every time. The pasted areas will stay the same, and I gave them enough surrounding room to never overlap. Unfortunately, I get many errors and am not good with VBA at all. I only know it from forums etc.

The source data is on "CopyData"
The Country Pivot Tables should go on "Country"

So my questions are:
1. How can I create a VBA script that creates and replaces pivot tables and can be used many time after (So maybe start with deleting old pivots?)
2. How can I keep the pivot table's names general? I won't know what the next pivot table will be called when it is run

Any help would be great!

Below is my code for one of the "macros" and it should create and replace two pivot tables.

   Sub Pivots_Country()
' Pivots_Country Macro
'
'
    Range("B215").Select
    ActiveWorkbook.Worksheets("Region").PivotTables("PivotTable10").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R2C2", TableName:="PivotTable10" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(2, 2).Select
    ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
        "PivotTable11").PivotFields("storeId"), "Count of storeId", xlCount
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("NewCountry")
        .Orientation = xlRowField
        .Position = 1
    End With
    Sheets("CopyData").Select
    Range("B10").Select
    ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable11").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R4C6", TableName:="PivotTable12" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(4, 6).Select
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("plan")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("planCode")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("NewCountry")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("storeId"), "Count of storeId", xlCount
    ActiveWindow.SmallScroll Down:=-7
    Range("B2:C56").Select
    Selection.Copy
    Sheets("Country").Select
    Range("B4").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=7
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveWindow.SmallScroll Down:=-2
    ActiveWindow.SmallScroll ToRight:=-1
    ActiveWindow.SmallScroll Down:=-1
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveWindow.SmallScroll Down:=-30
    Sheets("Sheet1").Select
    ExecuteExcel4Macro "(""PivotTable12"",""planCode"",3,FALSE,TRUE)"
    Range("F2:P52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Country").Select
    ActiveWindow.SmallScroll ToRight:=3
    ActiveWindow.SmallScroll Down:=-1
    ActiveWindow.SmallScroll ToRight:=2
    Range("R3").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=1
    Range("Q15").Select
    Sheets("Sheet1").Select
    Cells.Select
    Range("F2").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("J11").Select
    Sheets("CopyData").Select
End Sub

Best Answer

You really don't need to recreate the pivot tables. You need to find out why data is missing from the pivot tables after you refresh your data. This shoudl be a LOT less work.

I suspect the pivot tables are getting data from a range and that when you refresh the data the new data has more rows than the old data, so the pivot tables miss the extra rows.

The best way around this to ensure that you pivot tables are based on a range that has enough rows for all the current and future data you put into the sheet.

Inspect the pivot table.

click on pivot table
in ribbon>Pivot table tools>analyse>data>Change data source you can see what the range is (and change it)

Please let me know how you get on.

In the future you might consider using excel tables (also known as ListObjects)

For example

select the range that has your data
click the ribbon>Insert>Table button to convert the range to be an excel table.
change your pivot tables to reference data in the tables
Change how you refresh you data.

As you are new to excel, I wouldn't use Excel tables now, get it working first, and then convert it to use them.

I hope this helps. Harvey

How it works

I will try my best to explain how it works - please excuse my English.

The VBE will call a system function to create the password dialog box.
If user enters the right password and click OK, this function returns 1. If user enters the wrong password or click Cancel, this function returns 0.
After the dialog box is closed, the VBE checks the returned value of the system function
if this value is 1, the VBE will "think" that the password is right, hence the locked VBA project will be opened.
The code below swaps the memory of the original function used to display the password dialog with a user defined function that will always return 1 when being called.

Using the code

Please backup your files first!

Open the file(s) that contain your locked VBA Projects

Create a new xlsm file and store this code in Module1

code credited to Siwtom (nick name), a Vietnamese developer

Option Explicit

Private Const PAGE_EXECUTE_READWRITE = &H40

Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (Destination As Long, Source As Long, ByVal Length As Long)

Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _
        ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long

Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long

Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _
        ByVal lpProcName As String) As Long

Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _
        ByVal pTemplateName As Long, ByVal hWndParent As Long, _
        ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer

Dim HookBytes(0 To 5) As Byte
Dim OriginBytes(0 To 5) As Byte
Dim pFunc As Long
Dim Flag As Boolean

Private Function GetPtr(ByVal Value As Long) As Long
    GetPtr = Value
End Function

Public Sub RecoverBytes()
    If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6
End Sub

Public Function Hook() As Boolean
    Dim TmpBytes(0 To 5) As Byte
    Dim p As Long
    Dim OriginProtect As Long

    Hook = False

    pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")


    If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then

        MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
        If TmpBytes(0) <> &H68 Then

            MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6

            p = GetPtr(AddressOf MyDialogBoxParam)

            HookBytes(0) = &H68
            MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
            HookBytes(5) = &HC3

            MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
            Flag = True
            Hook = True
        End If
    End If
End Function

Private Function MyDialogBoxParam(ByVal hInstance As Long, _
        ByVal pTemplateName As Long, ByVal hWndParent As Long, _
        ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
    If pTemplateName = 4070 Then
        MyDialogBoxParam = 1
    Else
        RecoverBytes
        MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
                           hWndParent, lpDialogFunc, dwInitParam)
        Hook
    End If
End Function

Paste this code under the above code in Module1 and run it

Sub unprotected()
    If Hook Then
        MsgBox "VBA Project is unprotected!", vbInformation, "*****"
    End If
End Sub

Come back to your VBA Projects and enjoy.

Excel – How to avoid using Select in Excel VBA

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Or a multi-cell range:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

And it would be better like:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Pass ranges to your Subs and Functions as Range variables:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables:

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

Best Answer

Related Solutions

Excel – way to crack the password on an Excel VBA Project

How it works

Using the code

Excel – How to avoid using Select in Excel VBA

Some examples of how to avoid select

Related Topic