none
Create a macro to print a report as a PDF and then move to next report in drop-down list to create next report, whilst saving each report as the ticker name with date RRS feed

  • Question

  • 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

    Tuesday, August 25, 2015 12:35 AM

Answers

  • Hi kcusack,

    >> UT I don't know how to nest it in the PDF creation code to make it one macro.

    You could put your print pdf function in the For each statement. Something like below:

    Sub loopList()
    Dim inputRange As range
    'get the list of drop down list
    Set inputRange = Evaluate(range("A8").validation.Formula1)
    'loop the value in drop down list
        For Each Cell In inputRange
            'print to pdf
            pdfName = Cell.Value 'file name
            fileSaveName = Application.GetSaveAsFilename(pdfName, _
            fileFilter:="PDF Files (*.pdf), *.pdf")
            If fileSaveName <> False Then
            'active the sheet you want to print
            ActiveWorkbook.Sheets(pdfName).Activate
            activeSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                fileSaveName _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=True
            End If
        Next    
    End Sub

    With the code above, I assume that the name of your sheet is stored in the drop-down list, and the name of pdf will be named as sheet name. You could modify them with your own requirement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 25, 2015 7:38 AM

All replies

  • Hi kcusack,

    Based on your code, it seems that you did not know how to loop the drop-down list. Am I right? To achieve your requirement, I think you could loop your drop-down list to get the reports name, then define your report saved path and name, and save it as pdf.

    For looping the drop-down list, you could refer the code below:

    Sub loopList()
    Dim inputRange As range
    'get the list of drop down list
    Set inputRange = Evaluate(range("A8").validation.Formula1)
    'loop the value in drop down list
        For Each Cell In inputRange
            MsgBox Cell.Value
        Next    
    End Sub

    In the content of For each statement, you could define the report saved path and name, and save it as pdf.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 25, 2015 1:46 AM
  • Hi Edward,

    You would be 100% correct in saying that i know very little about anything. ha ha :)

    I put your code in as a separate macro and ran it and it works fantastic, BUT I don't know how to nest it in the PDF creation code to make it one macro. Does that make sense? 

    So, I want to hit the "Print Report to PDF" button and for all the reports to be created as separate PDFs and not having to individually print them everything they re-populate.

    Apologises for being so nieve.


    Tuesday, August 25, 2015 3:10 AM
  • Hi kcusack,

    >> UT I don't know how to nest it in the PDF creation code to make it one macro.

    You could put your print pdf function in the For each statement. Something like below:

    Sub loopList()
    Dim inputRange As range
    'get the list of drop down list
    Set inputRange = Evaluate(range("A8").validation.Formula1)
    'loop the value in drop down list
        For Each Cell In inputRange
            'print to pdf
            pdfName = Cell.Value 'file name
            fileSaveName = Application.GetSaveAsFilename(pdfName, _
            fileFilter:="PDF Files (*.pdf), *.pdf")
            If fileSaveName <> False Then
            'active the sheet you want to print
            ActiveWorkbook.Sheets(pdfName).Activate
            activeSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                fileSaveName _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=True
            End If
        Next    
    End Sub

    With the code above, I assume that the name of your sheet is stored in the drop-down list, and the name of pdf will be named as sheet name. You could modify them with your own requirement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 25, 2015 7:38 AM
  • Hello

    I find this code great to my own project, but it is posible change code to make rapport from single sheet.

    I need vba that change value on dropdown list on same sheet, make pdf rapport, loop to next value in list, make next rapport and so on to there is no more values on the list.

    My dropdown list on my sheet is VLOOKUP entry that change data on that sheet.

    Hope you can help me

    Sorry for my bad English

    Tuesday, September 27, 2016 6:48 AM
  • Hi KMarko,

    Thanks for your post.

    For your own issue, I would suggest you post a new thread, and then we could focus on your issue.

    In addition, it would be helpful if you could share us what you have done, and more information about your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 28, 2016 5:29 AM
  • Hi kcusack,

    >> UT I don't know how to nest it in the PDF creation code to make it one macro.

    You could put your print pdf function in the For each statement. Something like below:

    Sub loopList()
    Dim inputRange As range
    'get the list of drop down list
    Set inputRange = Evaluate(range("A8").validation.Formula1)
    'loop the value in drop down list
        For Each Cell In inputRange
            'print to pdf
            pdfName = Cell.Value 'file name
            fileSaveName = Application.GetSaveAsFilename(pdfName, _
            fileFilter:="PDF Files (*.pdf), *.pdf")
            If fileSaveName <> False Then
            'active the sheet you want to print
            ActiveWorkbook.Sheets(pdfName).Activate
            activeSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                fileSaveName _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=True
            End If
        Next    
    End Sub

    With the code above, I assume that the name of your sheet is stored in the drop-down list, and the name of pdf will be named as sheet name. You could modify them with your own requirement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    When I run that (changing my drop down location from A8 to E2), I get a Run-Time error '9': Subscript out of range. Can you help please? My VBA knowledge is non-existent 

    Monday, December 11, 2017 10:01 PM
  • Sub Main()
      Dim p$, pdf$, r As Range, c As Range, dd As Range
      
      p = "C:\Users\Katharine\Dropbox (Remerga)\Research\Company Research\Company Reports Kath Test\"
      Set dd = [E2]
      
      Set r = Range(dd.Validation.Formula1)
      For Each c In r
        dd = c  'Update lookups formulas by dropdown value
        pdf = p & c & " " & Format(Date, "yyyymmdd") & ".pdf"
        ActiveSheet.ExportAsFixedFormat xlTypePDF, pdf
      Next c
    End Sub


    Monday, December 11, 2017 11:34 PM