Create Pivot tables within a for loop

Question

I have a workbook with 70 plus sheets. Each sheet is created and named, and I put corresponding data into the worksheet using filters. The data range has potential to change week to week. I have written a vba program to do everything but create pivot tables within each worksheet using the unique filtered data. My question is, how do I create a pivot table within each worksheet inside the for loop? I have deducted that the problem in my code below is the source data section in the code. The data range for each tab will be different(columns stay same but number of rows will differ). Do I also need to rename the pivot tables as they iterate within the loop?

Sub Ptloop()

dim x as long
dim SorceRange as Range
dim k as long
'start of first generated work sheet
x=4
'number of worksheets
k=75


Set SourceRange = Range("A4", ActiveCell.End(xlDown).End(xlToRight))

For Each Worksheet In ActiveWorkbook.Sheets
If x <= k Then

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
   Sheets(x).SourceRange, Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:=Sheets(x).Cells(4, 21), TableName:="PivotTable3", DefaultVersion _
   :=xlPivotTableVersion14
 x = x + 1
Else
Exit For
End If
Next Worksheet
End Sub

You will have to use Dynamic Range. Please refer this article Create Dynamic Ranges with a Macro — skkakkar
Bring the SourceRange set inside the For Loop and qualify it to the ws. So Set SourceRange = Worksheet.Range("A4", Worksheet.Range("A4")l.End(xlDown).End(xlToRight)). You can name each PT with PivoTable & x, if you want them to be different. It's not necessary though, if they are all on different sheets. — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-07-11T19:00:33

There are few things that may be throwing the code off, or have the potential too.

Please see the refactored code below for a cleaner, easier to maintain program.

Option Explicit 'this forces variable declaration and is a "must use" programming tool

Sub Ptloop()

Dim ws as Worksheet 'make sure to qualify the worksheet to a variable
                      'using `Worksheet` could be problematic since its a reserved word in Excel for the `Worksheet` object itself

Dim SourceRange as Range

For Each ws In ThisWorkbook.Worksheets 'if you have a chart or macro sheet in your workbook `Sheets` will produce an error, if you only need actual Worksheets, use that directly

    If ws.Index > 3 Then 'use the index property, you only want worksheets after sheet 3

       With ws 'work directly with the object and assignment parentage to all objects

           'set the range inside the loop to capture the data for that specific sheet
           Set SourceRange = .Range("A4", .Range("A4").End(xlDown).End(xlToRight))

           'ActiveWorkbook may or may not refer to the workbook you really want to work with .. ThisWorkbook refers to the book running the code
           ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange, Version:=xlPivotTableVersion14). _
               CreatePivotTable TableDestination:=.Cells(4, 21), TableName:="PivotTable" & ws.Index, DefaultVersion _
               :=xlPivotTableVersion14

       End With

   End If

Next ws

End Sub

Create Pivot tables within a for loop

1 Answers