Automating the Creation of Reports

damien120

New Member
Joined
Feb 13, 2019
Messages
8
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)
https://www.mrexcel.com/forum/excel...mbining-do-loops-other-macros-statements.html


Thank you in advance and will greatly appreciate any help.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I guess you have your data in this way:

Excel Workbook
ABCD
1City#Target#
2Austin1Austin1
3Dallas2
4Houston3
5Chicago4
6Miami5
7Phoenix6
sheet1


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

Code:
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
    Next
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    MsgBox "End"
End Sub


Let me know if you have doubts.
 

damien120

New Member
Joined
Feb 13, 2019
Messages
8
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.

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

Thank you again!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
"C:\Temp"
 
Last edited:

damien120

New Member
Joined
Feb 13, 2019
Messages
8

ADVERTISEMENT

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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
You have to explain exactly what you want to add and I tell you how to do it.
 

damien120

New Member
Joined
Feb 13, 2019
Messages
8
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.

https://docs.google.com/spreadsheets/d/1Fes1tunuVJr0OKP3b2AljujIyQGJYx2J62CGDGsF0qo/edit?usp=sharing
---------------------------------------------------------

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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 MrExcel.com.
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 "mrexcel.com".
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
Top