Automating the Creation of Reports

[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 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.

 

[DanteAmor]

I guess you have your data in this way:

Excel Workbook
	A	B	C	D
1	City	#	Target	#
2	Austin	1	Austin	1
3	Dallas	2
4	Houston	3
5	Chicago	4
6	Miami	5
7	Phoenix	6
sheet1

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

Let me know if you have doubts.

 

[damien120]

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]

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"

 

[damien120]

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]

You have to explain exactly what you want to add and I tell you how to do it.

 

[damien120]

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.