Automating multiple PDF generation

[Muglerienne]

Hi,

I have managed to create a button from within my spreadsheet to automatically save the document to PDF. However, my next step (or perhaps it should have been my first) is to automatically generate multiple instances of what is essentially the same report. My spreadsheet is constructed fairly simply - data in tabular form on sheet 1 and report for PDF in sheet 2, file name from sheet 2 (cell E5) and the data in the rest of the report pulled from sheet 1 depending on the information pulled into E5. For example, E5 currently shows Sheet1!A2, but I need to then change that automatically to A3, A4, A5 and so on for over 900 different rows, and then have those PDFs all individually saved. Is there a way to do this?

Thanks in advance!

[HansV]

Welcome to Eileen's Lounge!

Here is a macro that you can use as starting point, and that you can assign to the command button:

Code: Select all

Sub Save2PDF()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    ' Last row in column A on Sheet1
    m = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    ' Loop through the cells in column A
    For r = 2 To m
        ' Fill E5 on Sheet2 with value of cell from Sheet1
        ws2.Range("E5").Value = ws1.Range("A" & r).Value
        ' Export Sheet2 to PDF
        ws2.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "\" & ws2.Range("E5").Value & ".pdf"
    Next r
    Application.ScreenUpdating = True
End Sub

[Muglerienne]

That worked perfectly!! Thank you!!

[Muglerienne]

Hi again.

I'm trying to combine 2 different macros now - the Save2PDF that you gave me, Hans, and another macro to autofit the height of merged cells (sometimes the height of the cell could go past the visible height of the screen). The problem is the autofit isn't working as it should and instead seems to be reducing my merged cells to the height of 1 row. I've tried several variations of the autofit macro that I found online (thanks to the various people who contributed), and this is the one that seems the best fit but as I am new to VBA, I'm not sure where I'm going wrong.

Any help you can give would be really appreciated.

Code: Select all

Sub Save2PDF()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim mw As Single


'Dim mw As Single

Dim cM As Range

Dim rng As Range

Dim cw As Double

Dim rwht As Double

Dim ar As Variant

Dim i As Integer

 

Application.ScreenUpdating = False

'Cell Ranges below, change to suit.

ar = Array("C46", "C47", "C48", "C49", "C50", "D51", "D61")

 

       For i = 0 To UBound(ar)

           On Error Resume Next

           Set rng = Range(Range(ar(i)).MergeArea.Address)

           rng.MergeCells = False

           cw = rng.Cells(1).ColumnWidth

           mw = 0

               For Each cM In rng

                  cM.WrapText = True

                  mw = cM.ColumnWidth + mw

               Next

          mw = mw + rng.Cells.Count * 0.66

          rng.Cells(1).ColumnWidth = mw

          rng.EntireRow.AutoFit

          rwht = rng.RowHeight

          rng.Cells(1).ColumnWidth = cw

          rng.MergeCells = True

          rng.RowHeight = rwht

      Next i

       
       
    Set ws1 = Worksheets("Data")
    Set ws2 = Worksheets("Full Report")
    ' Last row in column A on Sheet1
    m = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    ' Loop through the cells in column A
    For r = 7 To m
        ' Fill D6 on Sheet2 with value of cell from Sheet1
        ws2.Range("D6").Value = ws1.Range("B" & r).Value
        
        ' Export Sheet2 to PDF
        ws2.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "\" & ws2.Range("D6").Value & ws2.Range("G6").Value & ".pdf"
    Next r
    Application.ScreenUpdating = True
End Sub

Many thanks in advance!

Gillian

[HansV]

Are there vertically merged cells among those whose width you try to adjust? The code is intended for horizontally merged cells.

[Muglerienne]

No, they're only horizontally merged (merged across).

[HansV]

As far as I can tell, the code should work OK. Could you attach a small sample workbook to a reply with a few sample merged cells where the code goes wrong?

(It doesn't have to be the complete workbook you're working on - this is just about those merged cells).

[Muglerienne]

Will do, Hans, I just need to make the data anonymous.