Create a macro to print a report as a PDF and then move to next report in drop-down list to create n

[kath]

Hi guys

I'm hoping you can help me. I've very new to VBA (I started yesterday). I have a excel spreadsheet that produces a report from a number of various other sheets within the same document.

What I want to do is create a macro that once it runs, produces a PDF of each report and saves it as its individual ticker # name with the date and then runs the next report in the list, saving the following report as a PDF with its individual ticker # name and date, until the drop-down list of names is completed.

I have got this far with my macro (producing the report as a PDF and the ticker name automatically populates the 'save as' option as the individual reports ticker name). After this I'm stuck.

can anyone help me please?

cheers

Kath

Sub CreatePDFReport()
    pdfName = ActiveSheet.Range("G3")
    ChDir "C:\Users\Katharine\Dropbox (Remerga)\Research\Company Research\Company Reports Kath Test\" 'This is where youo set a defult file path.
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If

End Sub

 

[Chihiro]

From your post and the code. I'm guessing you are using Data validation list in a particular cell to generate report?

You'd need to reference the named range in the code. Link below would help.
http://www.excelforum.com/excel-pro...select-each-item-in-data-validation-list.html

But, I'd recommend using Spin Button control as it's much easier to code and maintain.

Below link will give you starting point.
http://stackoverflow.com/questions/...t-next-item-in-data-validation-list-using-vba

If you can upload sample file it'll be easier to help.