Automating the Creation of Reports


New Member
Feb 13, 2019
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 macro that saves the current workbook following the naming convention "Target #". In this case "Austin 1". I will then change the target city to Dallas and all of my other sheets will populate with data relevant to that city. Run the saving macro again and rinse and repeat. My question is, anyway I can make this process iterative? So that the target city cycles down the city column?

City # Target #
Austin 1 Austin 1

Dallas 2

Houston 3

Chicago 4

Miami 5

Phoenix 6

Save Macro
Sub save()

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

End Sub


Another user recommended
Sub Test()

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

End Sub

Link to original posting (asking slightly different question)

Thank you in advance and will greatly appreciate any help.

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I guess you have your data in this way:

Excel Workbook

Execute this macro.
Change "C:\trabajo\books" by your fPath
Change "Sheet1" By the name of your sheet with the cities

Sub Automating_Creation_Reports()
    Dim wPath As String, wFile As String
    Dim sh As Worksheet
    Dim u1 As Double, i As Double
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    wPath = "C:\trabajo\books\" 'your file path
    Set sh = Sheets("sheet1")   'your sheet name with cities
    If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
    If Dir(wPath, vbDirectory) = "" Then
        MsgBox "The path does not exists"
        Exit Sub
    End If
    u1 = sh.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To u1
        Application.StatusBar = "Saving file : " & i - 1 & " the : " & u1 - 1
        sh.Range("C2").Value = sh.Cells(i, "A").Value
        sh.Range("D2").Value = sh.Cells(i, "B").Value
        wFile = sh.Range("C2").Value & " " & sh.Range("D2").Value & ".xlsm"
        ActiveWorkbook.SaveCopyAs Filename:=wPath & wFile
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Let me know if you have doubts.
Upvote 0
Hello Dante,

Thank you for your response. I tried running it, but still getting the following error.

"Run-time Error '1004': Microsoft Excel cannot access the file 'C:\1 Austin.xlms'. There are several possible reasons:
1) The file name or path does not exist
2) The file is being used by another program (doubtful)
3) The workbook you are trying to save has the same name as the currently open workbook"

Another user recommended I make a dummy file and upload it to Dropbox. The link down below will take you to the folder in case you would like to see the actual file and reference points.

Thank you again!
Upvote 0
What did you put in this line:

wPath = "C:\trabajo\books" 'your file path

If you put this:
"C:\ "
Maybe you do not have permissions to save in that path, change "C:\ " for something like "C:\Temp" create a folder called
Last edited:
Upvote 0
Hello Dante,

Thank worked like a charm! Thank you so much.

Follow up question,
How flexible is the code you provided to adding other macros? I'm looking to add a hide rows and hide pages macro to help with the formatting. Is there a place I can insert the new code?

Thank you again, cervezas on me hermano! hahaha
Upvote 0
You have to explain exactly what you want to add and I tell you how to do it.
Upvote 0
Hello Dante

I made a dummy google worksheet to better explain what I am trying to do. Link is down below. If you have trouble accessing, or would like me to show you through another format please let me know.

What I am trying to do:
Perhaps a lack of skill, but my sheets reference the other sheets through an If function. (If city value matches then copy of value). I was having trouble with array and vlook up functions. The cells that don't match will display " ".

I was wondering if I could embed a "hide rows that have " " value" to show only the data relevant to location. I also have multiple sheets. If those sheets have no data, then a cell will display "Blank". I have a macro that will hide sheets with cell value "Blank". At that point I would like to save after formatting.

Unhide all rows and sheets and begins the process by, the city location moves down to city #2 , as you have helped me already.

Let me know what you think and greatly appreciate all the help.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back