• 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

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

AlanSidman

Active Member
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.
 

zrs

New Member
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
 

AlanSidman

Active Member
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

zrs

New Member
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
 

zrs

New Member
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
 

Luke M

Excel Ninja
@zrs
As Alan has discovered, please remember in the future to post links to cross posts, as it saves everyone some time.
 

zrs

New Member
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
 
Top