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