Combining Do Loops with other Macros statements

[damien120]

Hello all,

I am looking to make 1000+ reports based on a template. I have other sheets referencing the target city to update the data they report on. I have a master macro that calls on three other macros to format accordingly and save as "Location Report- Austin 1".

After creating the Austin report, I am looking to make one based out of the Dallas location. Thus I change the Target City to that all the other sheets will automatically update and then run the three macros (format and save file). My question is, how can I loop my macro so that it works its way down the 1,000+ cities. I am wondering if I can combine a Do Until statement and embed the other three macros within it.



[TABLE="width: 300"]
<tbody>[TR]
[TD]City[/TD]
[TD]#[/TD]
[TD]Target[/TD]
[TD]#[/TD]
[/TR]
[TR]
[TD]Austin
[/TD]
[TD]1[/TD]
[TD]Austin[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phoenix[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance and will greatly appreciate any guidance.

 

[mumps]

This macro assumes the city names are in column A.

Sub Test()
    Application.ScreenUpdating = False
    Dim city As Range
    For Each city In Range("A2", Range("A" & Rows.Count).End(xlUp))
        'your code here
    Next city
    Application.ScreenUpdating = True
End Sub

 

[damien120]

Thank you for mumps for the answer!

After the new city populates, I am trying to save the report. Following a naming convention so that the report is saved as "Austin 1". The code down below is what I added where you advised.

I am now getting a "Run-time error '1004'
Cannot access 'Austin 1.xlsm'

ActiveWorkbook.SaveAs Filename:="C:[File Path] & Range("C2").Text & Chr(32) & Range("D2").Text & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, Password:=vbNullString, WriteResPassword:=vbNullString, _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[mumps]

It is difficult to suggest a solution without seeing your macros and how your workbook is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[damien120]

Hey Mumps,

https://www.dropbox.com/sh/rbwwnu766n1rbbz/AACstj1TeFQXPHG1LveE9qfua?dl=0

This link goes to the folder with file. The first sheet contains a description of what I am trying to do. Please let me know if the link works or if you face any problems.

Thank you,
Damien Rodriguez

 

[mumps]

Could you upload a version of your file that includes all the macros you are currently using?