Need help with code to save file in a folder location based on a cell value

**msantucci** · 03-10-2015, 04:49 PM

Hello,

Here is what my code currently does - It loops through the values in column 4, filters the data, pastes the data to a new sheet, creates a PDF and saves the file in the file path that I specify in cell A12. Currently, all files created are saved within the same folder.

What I need the code to do - I need the code to catch the value of the first column (manager) and save the data within that specific folder. For example, all the data for "Employee A" will be saved in the folder called "John". I do not need the macro to create the individual folders, I can do that manually. What is the best way to do this? I believe I need to somehow tell the Filename argument to pick this up but I keep getting errors.

                With ActiveSheet
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=foldername & cell.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                End With

**msantucci** · 03-10-2015, 05:56 PM

I figured it out. I made a mypath2 variable which was just my original file path and the manager name added on the end. I then set mypath2 back to the original mypath variable before the loop to make sure mypath2 had the correct file path every time.

However, if anyone has a more efficient way to do this (less error prone or faster), I'd be happy to take the advice. Thanks for reading!

 
MyPath2 = MyPath & Range("A2").Value & "\"

With ActiveSheet
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyPath2 & cell.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                End With
                
                WSNew.Parent.Close False
                On Error GoTo 0
 

            my_range.AutoFilter Field:=FieldNum
            MyPath2 = MyPath