How to add Nested Loop to create multiple files

[praveenlal]

Hi VBA Experts,

Wrote 3 different codes and Called them via Control Box in VBA, so now I've to select Root Account and Click One Button to create a report Single Account.

Is there a way to create such files for 50-100 accounts where all Root Account names are under Column J and Year under Column 2 (We've only 2 years, 2021 and 2022) but multiple account names in Column J

I've called all 4 Macros via Command Button, so now we've click only once to create file for one account but I need one click to create 100 accounts. Is it possible in any way.

**********************************************************************************************************************************************************************************

Option Explicit

'TO CHANGE PIVOT FIELDS (ROOT ACCOUNT & YEAR) IN ALL 4 MASTER FILES (ALL FILES HAVING MULTIPLE PIVOT TABLES AND ALL PIVOTS HAVING ACCOUNT NAME AND YEAR)

Sub Account_Name_And_Year()                       
   
    Dim workbookNames As Variant
    workbookNames = Array("Boo1.xlsm", "Boo2.xlsm", "Boo3.xlsm", "Book4.xlsm")        'DEFINED WORKBOOK NAMES AS ARRAY
   
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
       
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
       
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Reports")
       
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 10).Value        'IT'LL CHANGE PIVOT FIELD ROOT ACCOUNT AS CELL RANGE ("J1") IN ALL 4 FILES"
       
        Dim year As String
        year = ws.Cells(2, 11).Value            'IT'LLL CHANGE PIVOT FIELD YEAR AS CELL RANGE ("K1") IN ALL 4 FILES"
       
        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            With pt
                With .PivotFields("Root Account")    'CHANGES PIVOT FIELD ROOT ACCOUNT ALL 4 MASTER FILES
                     .CurrentPage = rootAccount
                End With
                With .PivotFields("Year")        'CHANGES PIVOT FIELD YEAR IN ALL 4 MASTER FILES
                    .CurrentPage = year
                End With
            End With
        Next pt                       
       
    Next i                       
   
End Sub

***********************************************************************************************************************************************************

'ONCE PIVOT FIELDS (ROOT ACCOUNT & YEAR) CHANGES, COPY DATA FROM ALL PIVOT TABLES IN ALL 4 MASTER FILES AND PASTE IN TEMP FILE, SHEET 2021

Sub Data_2021()

Dim Book1 As Workbook
Dim Book2 As Workbook
Dim Book3 As Workbook
Dim Book4 As Workbook
Dim Temp As Workbook


Set Book1 = Workbooks.Open("C:\New Folder\Boo1.xlsm")
Set Book2 = Workbooks.Open("C:\New Folder\Boo2.xlsm")
Set Book3 = Workbooks.Open("C:\New Folder\Boo3.xlsm")
Set Book4 = Workbooks.Open("C:\New Folder\Boo4.xlsm")
Set Temp = Workbooks.Open("C:\New Folder\Template_File.xlsm")


'COPY DATA FROM ALL 4 WORKBOOKS AND PASTE IN DATA (2021) SHEET


Book1.Sheets("Analysis").Range("A13:M71").Copy
Temp.Sheets("Data (2021)").Range("B4").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book2.Sheets("Analysis").Range("S17:W17").Copy
Temp.Sheets("Data (2021)").Range("Z21").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book3.Sheets("Analysis").Range("D12:H12").Copy
Temp.Sheets("Data (2021)").Range("Z36").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book4.Sheets("Analysis").Range("B5:B15").Copy
Temp.Sheets("Data(2021)").Range("X16").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=True


Workbooks("Boo1.xlsm").Activate

Sheets("Analysis").Activate

Range("K2").Activate
 
Cells(2, 11) = "2022"

End Sub

**********************************************************************************************************************************************************

'THEN AGAIN RUN MACRO Account_Name_And_Year() TO CHANGE YEAR TO 2022 AND UPDATE ALL PIVOT TABLES IN ALL 4 MASTER FILES

'COPY DATA FROM ALL PIVOTS FROM ALL 4 MASTER FILES AND PASTE IN TEMP FILE, SHEET 2

Sub Data_2022()           

Dim Book1 As Workbook
Dim Book2 As Workbook
Dim Book3 As Workbook
Dim Book4 As Workbook
Dim Temp As Workbook
Dim FName As String
Dim Path As String

Application.DisplayAlerts = False

Set Book1 = Workbooks.Open("C:\New Folder\Boo1.xlsm")
Set Book2 = Workbooks.Open("C:\New Folder\Boo2.xlsm")
Set Book3 = Workbooks.Open("C:\New Folder\Boo3.xlsm")
Set Book4 = Workbooks.Open("C:\New Folder\Book4.xlsm")
Set Temp = Workbooks.Open("C:\New Folder\Template_File.xlsm")

Book1.Sheets("Analysis").Range("J1").Copy
Temp.Sheets("Data").Range("B1").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book2.Sheets("Analysis").Range("B17:B47").Copy
Temp.Sheets("Data").Range("T5").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book3.Sheets("Analysis").Range("B12:M12").Copy
Temp.Sheets("Data").Range("X37").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=False


Book4.Sheets("Analysis").Range("B5:B15").Copy
Temp.Sheets("Data").Range("X16").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=True, Transpose:=True
               

Range("A1").Activate

ActiveWorkbook.RefreshAll

Sheets("PPT").Activate

Range("A1").Activate

Path = "C:\New Folder\New folder\"
FName = Range("B1") & ".xlsm"

ActiveWorkbook.SaveAs Filename:=Path & FName

Sheets("PPT").Activate

Application.DisplayAlerts = True

MsgBox "COST ACTUALS REPORT IS CREATED FOR THIS ACCOUNT, PLEASE CLICK ON CREATE_PPT FOR POWERPOINT PRESENTATION"

Workbooks("Boo1.xlsm").Activate

Sheets("Analysis").Activate

Range("K1").Activate
 
Cells(1, 11) = "2021"

Range("J1").Activate


End Sub