• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to export used ranges of sheets to pdf

Z

zrs

New Member
Hi,

My example workbook is attached. In my original there are 5 contstant (3 of them are hided) sheets and various number (may lead to 200 sheets) of sheets after 5 constants. I need a vba to export the used ranges from 5th row to last used starting from the 2nd one to the last to a single pdf file but by passing 3rd, 4th & 5th sheets even to be described below and in attachment as well. My constant sheets string:

1st Sheet name “CALC” (Unhide)
2nd Sheet name “INDEX” (Unhide)
3rd Sheet name “HELPER1” (Hide)
4th Sheet name “HELPER2” (Hide)
5th Sheet name “HELPER3” (Hide)

The rest of the Sheets starting after "HELPER3" have different names of Hotels.

Thanks in advance for your kindest helps.
 

Attachments

  • eg1 29.04.2019.xlsx
    12.2 KB · Views: 2
Confused. Which sheets to be printed? But consolidated into one master? Your explanation is confusing. Please try to be specific and name the sheets that need to consolidated and printed. Use the names of the sheets to be sure.
 
Dear Alan,

Thanks for your concern. I need sheets starting from INDEX (2nd Sheet) till last sheet to be printed (Sheets CALC & HELPER1-2-3 excluded). Last sheet number is variable can be lead to 150-200 and all ordered after HELPER3. Sheets with HELPER title are hided in my original workbook and no need to print. The pdf file of all printable sheets must be start from row A5 till the last used row (tried to explain in my attached file) and not seperated in a single pdf as pages.

Hope these helps. best Regards
 
Code: 
Option Explicit

Sub PrintPDF()
    Dim ws As Worksheet
    Dim lr As Long
    For Each ws In Worksheets
        If ws.Name <> "CALC" And ws.Name <> "HELPER1" And ws.Name <> "HELPER2" _
           And ws.Name <> "HELPER3" Then
            lr = ws.Range("B" & Rows.Count).End(xlUp).Row
            ws.Range("A5:F" & lr).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
        End If
    Next ws
    MsgBox "completed"

End Sub
 
  • Love
Reactions: zrs
Dear Alan,

Kindest thanks for your code, works excellent to build seperate pdf pages. Is it possible to gather (INDEX + HOTEL X + HOTEL Y) them in one pdf file as pages and save to the same directory of excel sheet?

Regards
 
Dear Alan,

I checked your referenced page but not get a right solution to print in a single pdf. I tried several possibilities but the the pdf file has lots of mistakes can not transfer the data on excel in well format and several blank pages occured. Hope to hear from you again.

Regards
 
@zrs
As Alan has discovered, please remember in the future to post links to cross posts, as it saves everyone some time.
 
Dear Alan,
One more thanks for your support. I compiled your code with your referenced web site code as in below, now works fine.
Regards
Code: 
Option Explicit

Private Sub PrintPDF()

Application.Run "StopAllEvents"

If Sheets.Count >= 6 Then

Dim x As Long
Sheets("INDEX").Select
For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).Name <> "CALC" And Sheets(x).Name <> "HELPER1" And Sheets(x).Name <> "HELPER2" And Sheets(x).Name <> "HELPER3" Then
    Sheets(x).Select Replace:=False
    End If
Next x

Dim str As String, myfolder As String, myfile As String, sht As Worksheet, answer As String
Dim lr As Long

str = "Do you want to save these sheets to a single pdf file?" & Chr(10)
For Each sht In ActiveWindow.SelectedSheets
str = str & sht.Name & Chr(10)
lr = sht.Range("B" & Rows.Count).End(xlUp).Row
Next sht

answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

With Application.FileDialog(msoFileDialogFolderPicker)
If .Show <> -1 Then
MsgBox "Save Cancelled!", 48, "WARNING"
Exit Sub

myfolder = .SelectedItems(1) & "\"
If .Show <> -1 Then
MsgBox "Save Cancelled!", 48, "WARNING"
Exit Sub
End If
End If
End With

myfile = InputBox("Enter filename", "Save as..")

ActiveSheet.Range("A5:Z" & lr).ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Else

MsgBox "No Hotel to Print!", 48, "WARNING"

Exit Sub

End If

Sheets("CALC").Select

Application.Run "ResetAllEvents"

End Sub
 
You must log in or register to reply here.
Back
Top