VBA to save reports, generated using macros, to specific folders

nfoster
Jul 20th 2017
Thread is marked as Resolved.

Jul 20th 2017

#1

I have a set of macros to generate multiple reports which works fine. However, I would like to include a VBA script to save the generated reports into specific folders depending on the name of the report that is being generated. When I generate reports using macros, the files are saved as per the name of the city to the local drive. What I'm looking for is to have the macro search up the corresponding folder (matching the name of the report) in the local drive and place it under a folder with the current date. For example, my macro saves all the reports (city names) for my client AFD to D: after which I have to manually place the files to its respective folders. I would like for it to search the directory for a folder called AFD > city name > current month > current date > save. There are a total of 25 clients and 175 cities.

Here's the VBA code I use to generate the reports. Any help is greatly appreciated.

Code

Sub RespFilterDenied()
    Dim c As Range
    Dim Rng As Range
    Dim lr As Long
        
        lr = Cells(Rows.Count, "R").End(xlUp).row
        Set Rng = Range("A1:X" & lr)
        
        Range("W1:W" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
        
        For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
            With Rng
                .AutoFilter
                .AutoFilter field:=23, Criteria1:=c.Value
                .SpecialCells(xlCellTypeVisible).Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                ActiveSheet.Paste
                For Each R In ActiveWindow.RangeSelection.Rows
        R.RowHeight = 36
Next R
            End With
        Next c
        Sheets("Sheet1").Delete
        ActiveWorkbook.Save
        ActiveWorkbook.Close
End Sub

Display More

trunten

Professional

Reactions Received

22

Points

4,237

Posts

819

Operating System

Windows

Excel Version

Office 365
Jul 20th 2017
- #2
Re: VBA to save reports, generated using macros, to specific folders

Can't really help without knowing where in the sheet I would pull the client/city name from. however, this should help: https://msdn.microsoft.com/en-…kbook-saveas-method-excel

edit: just as an observation, if you want to speed up your code a little I would consider using advanced filter to speed up the "copy/paste" process, and you could set row height without the loop for the entire range. An additional speed boost could be had from turning off screen updating (assuming you aren't doing that from a parent sub).
nfoster

Beginner

Points

35

Posts

5
Jul 21st 2017
- #3
Re: VBA to save reports, generated using macros, to specific folders

Hi Trunten, Thank you for your inputs to help speed up my code. I'm in the process of making those changes. I have attached a sample document to give you an idea where the client & city columns are on my report. Thanks. NF.
Files

nfoster.xlsx 12.79 kB – 79 Downloads

Jul 24th 2017

#4

Re: VBA to save reports, generated using macros, to specific folders

Can't test this at the moment as I'm not currently near a machine with excel. But something along these lines should sort you out. hopefully it's easy enough to follow so you can tweak where required.

Code

Sub RespFilterDenied() 
    Dim c As Range 
    Dim Rng As Range 
    Dim lr As Long 
    Dim fn as string
    Dim clientName as string
    Dim cityName as string
     
    lr = Cells(Rows.Count, "R").End(xlUp).row 
    Set Rng = Range("A1:X" & lr) 
     
    Range("W1:W" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True 
     
    For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp)) 
        With Rng 
            .AutoFilter 
            .AutoFilter field:=23, Criteria1:=c.Value 
            .SpecialCells(xlCellTypeVisible).Copy 
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value 
            ActiveSheet.Paste 
            clientName = Range("E2").value
            cityName = Range("I2").value
            For Each R In ActiveWindow.RangeSelection.Rows 
                R.RowHeight = 36 
            Next R 
        End With 
    Next c 
    Sheets("Sheet1").Delete 
    fn = "D:\" & clientName & "\" & cityName & "\" & format(date,"mmmm") & "\" & replace(date,"/","-") & "\" & cityName & ".xlsx"
    ActiveWorkbook.SaveAs fn, xlWorkbookNormal
    ActiveWorkbook.Close 
End Sub

Display More

nfoster

Beginner

Points

35

Posts

5
Jul 24th 2017
- #5
Re: VBA to save reports, generated using macros, to specific folders

Trunten - Your resolution works as expected and saved me a lot of trouble. Thank you very much.
nfoster

Beginner

Points

35

Posts

5
Aug 10th 2017
- #6
Re: VBA to save reports, generated using macros, to specific folders

Hi Trunten - I have had to include a Year folder into my structure for easier management. I tried editing the code myself but only managed to generate an error. Could you please help me with this?
trunten

Professional

Reactions Received

22

Points

4,237

Posts

819

Operating System

Windows

Excel Version

Office 365
Aug 10th 2017
- #7
Re: VBA to save reports, generated using macros, to specific folders

Yeah. Let me know the details and I'll have a look

Aug 11th 2017

#8

Re: VBA to save reports, generated using macros, to specific folders

Thank you. Well, its the same code that you helped with earlier wherein it would save the generated reports into existing folders by looking up the client name in column B, city name in column I and then search for current year, then current month, and finally current date where the report should be saved. Presently the macro is configured to save the file in the following structure: client name> city name > current month > current date > save. I would like to modify it to save it by client name > city name > current year > current month > current date > save. I am reattaching the report template for reference. Your help is much appreciated.

Code

Sub RespFilterPartPaid()    Dim c As Range
    Dim Rng As Range
    Dim lr As Long
    Dim fn As String
    Dim clientName As String
    Dim cityName As String
        
        lr = Cells(Rows.Count, "R").End(xlUp).row
        Set Rng = Range("A1:X" & lr)
        
        Range("W1:W" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
        
        For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
            With Rng
                .AutoFilter
                .AutoFilter field:=23, Criteria1:=c.Value
                .SpecialCells(xlCellTypeVisible).Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                ActiveSheet.Paste
                clientName = Range("B2").Value
                cityName = Range("I2").Value
                For Each R In ActiveWindow.RangeSelection.Rows
                    R.RowHeight = 36
                Next R
            End With
        Next c
        Sheets("Sheet1").Delete
        fn = "\\inchenfl01\ADP MANAGEMENT\Affiliate Report\Output\Reports\" & clientName & "\" & cityName & "\" & Format(Date, "mmmm") & "\" & Replace(Date, "/", "") & "\" & "Claims By Status_Denied" & ".xlsx"
        ActiveWorkbook.SaveAs fn, xlWorkbookNormal
        ActiveWorkbook.Close
End Sub

Display More

Aug 11th 2017

#9

Re: VBA to save reports, generated using macros, to specific folders

Code

Sub RespFilterPartPaid()    
Dim c As Range 
    Dim Rng As Range 
    Dim lr As Long 
    Dim fn As String 
    Dim clientName As String 
    Dim cityName As String 
     
    lr = Cells(Rows.Count, "R").End(xlUp).row 
    Set Rng = Range("A1:X" & lr) 
     
    Range("W1:W" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True 
     
    For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp)) 
        With Rng 
            .AutoFilter 
            .AutoFilter field:=23, Criteria1:=c.Value 
            .SpecialCells(xlCellTypeVisible).Copy 
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value 
            ActiveSheet.Paste 
            clientName = Range("B2").Value 
            cityName = Range("I2").Value 
            For Each R In ActiveWindow.RangeSelection.Rows 
                R.RowHeight = 36 
            Next R 
        End With 
    Next c 
    Sheets("Sheet1").Delete 
    fn = "\\inchenfl01\ADP MANAGEMENT\Affiliate Report\Output\Reports\" & clientName & "\" & cityName & "\" & Format(Date, "yyyy") & "\" & Format(Date, "mmmm") & "\" & Replace(Date, "/", "") & "\" & "Claims By Status_Denied" & ".xlsx" 
    ActiveWorkbook.SaveAs fn, xlWorkbookNormal 
    ActiveWorkbook.Close 
End Sub

Display More

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.