Adding Loop to a recorded macro

[praveenlal]

Hi All,

Need your all help to add Loop in this Code. I've list of 50+ clients for which I've to send reports every Month. I've tried coding for 2 clients and its working fine but I've to do it for 50+ Clients, also file name should be same as C1, C2.... C55 in TMP file which I've already done but I want to add Loop here so that I don't have to click 50+ times to create 50+ reports. Can any VBA expert help me out here.

Sub Get_Data2022()

Dim A1 As Workbook
Dim A2 As Workbook
Dim A3 As Workbook
Dim TMP As Workbook
Dim FName As String
Dim Path As String

Application.DisplayAlerts = False

Set A1 = Workbooks.Open("C:\Master1.xlsm")
Set A2 = Workbooks.Open("C:\Master2.xlsm")
Set A3 = Workbooks.Open("C:\Master3.xlsm")
Set TMP = Workbooks.Open("C:\Template_Blank.xlsx")

Path = "C:\New folder\"

A1.Sheets("Analysis").Range("J1").Copy
TMP.Sheets("Data").Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

A2.Sheets("Dashboard").Range("B17:B47").Copy
TMP.Sheets("Data").Range("T5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

A3.Sheets("Pivot").Range("B12:M12").Copy
TMP.Sheets("Data").Range("X37").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

Range("A1").Activate

FName = Range("C1").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FName, xlOpenXMLWorkbook _

Application.DisplayAlerts = True
ActiveWorkbook.Close

MsgBox "Excel Report Created Successfully for this account"

A1.Sheets("Analysis").Range("J2").Copy
TMP.Sheets("Data").Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

A2.Sheets("Dashboard").Range("B17:B47").Copy
TMP.Sheets("Data").Range("T5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

A3.Sheets("Pivot").Range("B12:M12").Copy
TMP.Sheets("Data").Range("X37").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

Range("A1").Activate

FName = Range("C2").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FName, xlOpenXMLWorkbook _

Application.DisplayAlerts = True
ActiveWorkbook.Close

MsgBox "Excel Report Created Successfully for this account"


End Sub

 

[ranman256]

loop thru 50 client workbooks?
or
loop thru 50 rows in 1 sheet?
or
loop thru 50 sheets in 1 workbook?

 

[praveenlal]

I've 3 Master Sheets (Master1 having names of all 50 clients) and want to paste data in Template File from all 3 master files and then SaveAs file name as Client Name (and if possible can I have current Month & Year in File Name i.e "Amazon Mar 2022")

loop thru 50 client workbooks?
or
loop thru 50 rows in 1 sheet?
or
loop thru 50 sheets in 1 workbook?

 

[praveenlal]

loop thru 50 client workbooks?
or
loop thru 50 rows in 1 sheet?
or
loop thru 50 sheets in 1 workbook?

Yes, Loop through 50 rows in 1 sheet (master1 having all client names in Sheet Reports - Range A2:A51) and then paste data in template file (from all 3 master files)