vba excell to single pdf

[Mngazija]

Hi guys

I am very new to VBA, 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 one PDF of each report and saves into one PDF in the order that from the drop-down list, show the page number in the same order and show data as well. Also, save the PDF with a given name. My dropdowlist is in the cell “I4” and the range of printing is “B2:G36” .I would like the pdf to be saved in the same folder as the workbook automatically.

 

[John_w]

Does this macro do what you want? It creates a single PDF named "Report.pdf" in the same folder as the workbook, with "Page 1", "Page 2", etc. in the footer of each page. You must run the macro from the sheet of the I4 cell dropdown, because it expects that cell to be on the active sheet.

Public Sub Create_PDF_Report()

    Dim PDFfullName As String
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim copyRange As Range
   
    With ActiveWorkbook
   
        'Cell containing data validation in-cell dropdown
   
        Set dataValidationCell = .ActiveSheet.Range("I4")
       
        'Range to be copied to temporary PDF sheet for each dropdown value
       
        Set copyRange = .ActiveSheet.Range("B2:G36")
   
        'PDF file name
       
        PDFfullName = .Path & "\Report.pdf"
       
        'Add temporary sheet for PDF output
       
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        
        'Set destination cell on temporary sheet to same column as source data, in order to copy same column widths
        
        Set destCell = PDFsheet.Range("B1")
       
    End With
        
    'Source of data validation list
   
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    
    'Loop through each data validation value to update copyRange values
   
    For Each dvValueCell In dataValidationListSource
   
        dataValidationCell.Value = dvValueCell.Value
       
        'Copy cell formats, column widths and cell values to next cell in temporary PDF sheet
       
        copyRange.Copy
        destCell.Select
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
        'Use Format Painter to apply row heights
       
        copyRange.EntireRow.Copy
        With PDFsheet
            .Range(destCell, .Cells(.UsedRange.Rows.Count, 1)).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
           
        'Add page break and update destination cell to next row
       
        With PDFsheet
            .HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
            Set destCell = .Cells(.UsedRange.Rows.Count + 1, destCell.Column)
        End With
       
    Next
       
    'Add page footer to temporary sheet, save it as a PDF and then delete it
   
    With PDFsheet
        .PageSetup.Orientation = xlPortrait 'xlLandscape
        .PageSetup.CenterFooter = "Page &P"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
   
End Sub

 

[Mngazija]

Does this macro do what you want? It creates a single PDF named "Report.pdf" in the same folder as the workbook, with "Page 1", "Page 2", etc. in the footer of each page. You must run the macro from the sheet of the I4 cell dropdown, because it expects that cell to be on the active sheet.

Public Sub Create_PDF_Report()

    Dim PDFfullName As String
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim copyRange As Range
  
    With ActiveWorkbook
  
        'Cell containing data validation in-cell dropdown
  
        Set dataValidationCell = .ActiveSheet.Range("I4")
      
        'Range to be copied to temporary PDF sheet for each dropdown value
      
        Set copyRange = .ActiveSheet.Range("B2:G36")
  
        'PDF file name
      
        PDFfullName = .Path & "\Report.pdf"
      
        'Add temporary sheet for PDF output
      
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
       
        'Set destination cell on temporary sheet to same column as source data, in order to copy same column widths
       
        Set destCell = PDFsheet.Range("B1")
      
    End With
       
    'Source of data validation list
  
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
   
    'Loop through each data validation value to update copyRange values
  
    For Each dvValueCell In dataValidationListSource
  
        dataValidationCell.Value = dvValueCell.Value
      
        'Copy cell formats, column widths and cell values to next cell in temporary PDF sheet
      
        copyRange.Copy
        destCell.Select
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
        'Use Format Painter to apply row heights
      
        copyRange.EntireRow.Copy
        With PDFsheet
            .Range(destCell, .Cells(.UsedRange.Rows.Count, 1)).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
          
        'Add page break and update destination cell to next row
      
        With PDFsheet
            .HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
            Set destCell = .Cells(.UsedRange.Rows.Count + 1, destCell.Column)
        End With
      
    Next
      
    'Add page footer to temporary sheet, save it as a PDF and then delete it
  
    With PDFsheet
        .PageSetup.Orientation = xlPortrait 'xlLandscape
        .PageSetup.CenterFooter = "Page &P"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
  
End Sub

Thank you for your reply however as I pasted the code it created thousands of reports which are empty ,what I want is if in the dropdowlist which is in cell "I4" displays let say number 1-5,then it should create reports for students 1-5 only and ignore empty reports. Currently I run the code and doesn't have an end, I forced to stop as it keep creating empty reports including the reports from dropdowlist.

 

[John_w]

The macro currently creates a single PDF for all cell values in the source of the data validation dropdown list. So if the source is =Sheet2!$G$2:$G$5 (4 cells) it will loop through all 4 cells, updating the active sheet and copying cells B2:G36 four times to a temporary sheet and saving the final temporary sheet as a PDF.

Do you want the macro to loop through all the cell values of the data validation list source?

what I want is if in the dropdowlist which is in cell "I4" displays let say number 1-5,then it should create reports for students 1-5 only and ignore empty reports.

It's not clear to me what list of values are displayed in your dropdown. Are these values actually ranges like "1-5", then "6-10", "11-15", etc.? Or are they single numbers "1", "2", "3" etc? Perhaps post a screenshot so that we can see the dropdown.

How would the macro know that a report is empty? Is there a cell that is empty or contain a particular value that indicates an empty report?

 

[Mngazija]

Thanks for your quick response,I have attached the screenshort,on the right hand side,when you click the box under S/N that is where the dropdowlist is,the dropdownlist will depends on the number of students filled in that particular class,so the numbers appears as 1,2,3 etc.,however those numbers are the linked from another sheet.So I was thinking the pdf created should reflect how many numbers are in the dropdowlist.In connection to that the macro for pdf creation should consider the print area.right now with the macro provided doesnt reflect the page set up of the report,it just mix the pages.
I hope now is clear for your help.

 

[John_w]

Sorry, but your screenshot and further explanation doesn't really clarify your request. Also, you didn't answer all my questions. To proceed further I would need a sample workbook with anonymised data uploaded to a file-sharing site such as Google Drive or Dropbox.

You now say the PDF should reflect the Page Setup Print Area, whereas your OP said it should be the cells B2:G36. To output the Print Area instead to the PDF replace this line:

        Set copyRange = .ActiveSheet.Range("B2:G36")

with this line:

        Set copyRange = .ActiveSheet.Range(.ActiveSheet.PageSetup.PrintArea)

 

[Mngazija]

Thanks,solved