Report Printing Macro Help

I am very new to Macros and VBA and this is my first post on the forum so please forgive me if it is in the wrong place.

I am trying to use a macro in excel 2010 to save over 300 individual quarterly reports as independently named PDFs for our clients. This macro should select a name from a data validation list in cell G1, which populates the reports, and then save an array of sheets as a pdf. I would like for it to name the pdf file using the value in G1.


This is the code I am using to print and save the PDF files.

' code to slect sheets to print and set them as the active sheets
    Sheets(Array("A", "B", "C", "D", "E")).Select
    Sheets("A").Activate
    ChDir "C:\Users\JD\Desktop\Reports" ' code for where to save the pdf reports
' the following is the code to save the reports as pdf documents
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\JD\Desktop\Reports\" & Range("G1").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

I believe the the save function works but what I cannot figure out is how to get the macro to cycle through the data validation list giving enough time to populate each client's quarterly report before printing it. Each time I try it only prints one and never changes the value in cell G1 I tried to add some time delays but they did not help at all.

Thanks for any help you can give me.

My entire code albeit wrong:

Sub printing()
    Dim c As Range
    For Each c In Range("A2:A341")
        Range("g1") = c
        Application.Calculate
         'here the name of the module which does the printing
      newHour = Hour(Now())
      newMinute = Minute(Now())
      newSecond = Second(Now()) + 5
      waitTime = TimeSerial(newHour, newMinute, newSecond)
      Application.Wait waitTime
' code to slect sheets to print and set them as the active sheets

    Sheets(Array("A", "B", "C", "D", "E")).Select
    Sheets("A").Activate
    
    ChDir "C:\Users\JD\Desktop\Reports" ' code for where to save the pdf reports
' the following is the code to save the reports as pdf documents
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\JD\Desktop\Reports\" & Range("G1").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
        newHour = Hour(Now())
      newMinute = Minute(Now())
      newSecond = Second(Now()) + 3
      waitTime = TimeSerial(newHour, newMinute, newSecond)
      Application.Wait waitTime
      
      Sheets(Control).Select
      
    Next
    MsgBox "You're done!"
End Sub

Try this:

Option Explicit

Sub CyclePrinting()
Dim c As Range, savePATH As String

savePATH = "C:\Users\JD\Desktop\Reports\"           'remember the final \

With Sheets("Control")
    For Each c In .Range(.Range("G1").Validation.Formula1)
        .Range("g1") = c.Value
        Do Until Application.CalculationState = xlDone: DoEvents: Loop
    
    ' code to slect sheets to print and set them as the active sheets
        Sheets(Array("A", "B", "C", "D", "E")).Select
        Sheets("A").Activate
    
    ' the following is the code to save the reports as pdf documents
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=savePATH & c.Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next c
End With

MsgBox "You're done!"
End Sub

NOTE: You should edit your post #1 and add code tags, like I've shown here in this post. (instructions in my signature)

Read through the Forum Rules (link above)... lots of useful tips.