Add loop to create multiple reports

[praveenlal]

I have multiple pivots tables in all 3 files (Book1, 2, 3). This code selects the Account Name and Year 2021 from pivot fields then copy data from pivot to Temp file, sheet Data 2021. Then again it changes pivot field to Year 2022 and paste data in Temp file, sheet Data 2022

I have created this code to save one file (Cells(1, 10) = "J1"). I have 50+ Account Names in column J. Can I add loop here to create reports for all 50+ accounts.


Sub Account_Names_Year()

Dim workbookNames As Variant
workbookNames = Array("Book1.xlsm", "Book2.xlsm", "Book3.xlsm")

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("Analysis")

Dim rootAccount As String
rootAccount = ws.Cells(1, 10).Value

Dim year As String
year = ws.Cells(1, 11).Value

Dim pt As PivotTable
For Each pt In ws.PivotTables
With pt
With .PivotFields("Account Name")
.CurrentPage = rootAccount
End With
With .PivotFields("Year")
.CurrentPage = year
End With
End With
Next pt


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

Set Book1 = Workbooks.Open("C:\VB Code\Book1.xlsm")
Set Book2 = Workbooks.Open("C:\VB Code\Book2.xlsm")
Set Book3 = Workbooks.Open("C:\VB Code\Book3.xlsm")
Set Temp = Workbooks.Open("C:\VB Code\Template_File.xlsm")


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

Book1.Sheets("Analysis").Activate


year = ws.Cells(2, 11).Value

For Each pt In ws.PivotTables
With pt
With .PivotFields("Year")
.CurrentPage = year
End With
End With
Next pt

Next i


Dim FName As String
Dim Path As String

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

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

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

ActiveWorkbook.RefreshAll

Temp.Sheets("Analysis").Activate

Range("A1").Activate

Path = "C:\VB Code\New folder\"
FName = ws.Cells(1, 10).Value & ".xlsm"

ActiveWorkbook.SaveAs Filename:=Path & FName

Application.DisplayAlerts = True

MsgBox "Cost Actuals Excel Report Created Successfully for this account"

End Sub

 

[praveenlal]

Getting a serious issue here, this code paste the 2022 data into 2021 sheet