Multiple Range to one PDF from 4 sheets

[atuljadhavnetafim]

Hi,

I have multiple sheets in my file and I need to export as PDF from the selected range of selected sheets,
I have used the below code but it is not working properly, PDF generated but range not pick from as mentioned in the below code.
it takes from first sheet all data.

Please suggest if I need to arrange my sheets accordingly or need to change the sheet name in the below code.
I want to create a single PDF with 4 pages.

Sub CreatePDF()

Set ws1 = Sheets("Anantapur")
 ws1.PageSetup.PrintArea = ws1.Range("C3:R37").Address
 
  Set ws2 = Sheets("Singataluru")
 ws2.PageSetup.PrintArea = ws2.Range("C3:R25").Address
 
  Set ws3 = Sheets("Singataluru-3")
 ws3.PageSetup.PrintArea = ws3.Range("C3:R10").Address
  
  Set ws4 = Sheets("Tarikere")
 ws4.PageSetup.PrintArea = ws4.Range("C3:R35").Address
 
        ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="D:\Email\temp.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End Sub

 

[sijpie]

Check VBA code to save Excel files as PDF - Excel Off The Grid

You will need to select all the sheets, then use ActiveSheet.ExportAsFixedFormat instead of ThisWorkbook.ExportAsFixedFormat

 

[John_w]

To create a multiple page PDF from disparate ranges you have to copy each range to a temporary sheet and add a page break after each range, then export the temporary sheet.

Public Sub Create_PDF()

    Dim PDFranges As Variant, PDFrange As Variant
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim copyRange As Range
   
    PDFranges = Array("Anantapur!C3:R37", "Singataluru!C3:R25", "Singataluru-3!C3:R10", "Tarikere!C3:R35")
   
    With ActiveWorkbook
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
    Set destCell = PDFsheet.Range("A1")
   
    For Each PDFrange In PDFranges
        Set copyRange = ActiveWorkbook.Worksheets(Split(PDFrange, "!")(0)).Range(Split(PDFrange, "!")(1))
       
        'Copy cell formats and column widths
        copyRange.Copy
        destCell.Select
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        With PDFsheet
            .HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
            Set destCell = .Cells(.UsedRange.Rows.Count + 1, 1)
        End With
    Next
   
    Application.CutCopyMode = False
   
    PDFsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="D:\Email\temp.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

    Application.DisplayAlerts = False
    PDFsheet.Delete
    Application.DisplayAlerts = True
   
End Sub

 

[atuljadhavnetafim]

Hi John

i have checked this code but the problem is,
1) My range have formula so we need to pastespecial the number with original format
2) some table go to next page so we need to fix it to once page only

please change so i can test it.

 

[John_w]

Hi John

i have checked this code but the problem is,
1) My range have formula so we need to pastespecial the number with original format
2) some table go to next page so we need to fix it to once page only

Replace:

        'Copy cell formats and column widths
        copyRange.Copy
        destCell.Select
        destCell.Worksheet.Paste
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

with:

        copyRange.Copy
        destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        destCell.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

If that doesn't give the required result then record a macro whilst you copy and paste special and post the generated macro here.

I don't understand the second issue. Do you mean some tables occupy more than 1 page and you want them on only 1 page? How will they go on 1 page? By deleting rows or by copying and pasting only a certain number of rows? Or something else?

 

[atuljadhavnetafim]

 

[John_w]

Insert destCell.Select after the copyRange.Copy