Print multiple sheet as single pdf page

[elsuji]

24889Hi,

I have 7 work sheet in excel (Sheet1, Sheet2, Sheet3, Sheet4, Sheet5,Sheet6, Sheet7) . In these sheets except Sheet3 all other sheets should print in single PDF with proper alignment. Can any one please help me how to write the code for this


The sample file is attached with this post.

[paulked]

Here's a starter:


Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet5", "Sheet6", "Sheet7")).PrintOut _
Copies:=1, Collate:=True, IgnorePrintAreas:=False

[Kenneth Hobs]

1. Each worksheet prints with at least one pdf page depending on your print area in Page Layout setting or lack of one. So, for 6 worksheets, you can do one pdf per worksheet. Each would have at least one page each.

2. Or, you can generate a pdf with all 6 worksheets with at least 6 pages total.

3. If you really wanted just one pdf page for all 6 worksheets, you need to create a worksheet with the parts from each that you want. The Page Layout settings will need to have it set to fit it all with zoom.

Which of the 3 ways did you want? Looking at your file, I am thinking it would be (2) rather than (3) as your title stated.

[elsuji]

If the sheet it is in hide then how to write the program

[elsuji]

Hi

Your code is working. But the page alignment is not same for all the page.



And how to use the following code with above code and how to do the page setup

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"a.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

[Kenneth Hobs]

Please paste code between code tags. Click the # icon to insert them.


Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet5", "Sheet6", "Sheet7")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Environ("temp") & "\a.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

Set the page setting manually in Page Layout ribbon. Select all of your sheets and then set them if you need them to be the same.

[elsuji]

I want this should print even the sheet is hide also. Can you pls tell me how to do that

[Kenneth Hobs]

If you we know the visible property states, we can do it efficiently. I will look at the file.

Sheet8 was the only one with visible property =xlSheetHidden. It was not in the list of ones you wanted to pdf.

I guess we could code it to get the visible states for all, make all xlSheetVisible, make the pdf, and then restore each sheet to previous state. Are the sheet's visible property going to change?

[elsuji]

Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?3661-Kenneth-Hobs)

Hi Kenneth,

I am attaching my file here . In this the "Data" sheet is only visible and all other sheets are hide.

Please find the attachment.

[Kenneth Hobs]

None of those sheets are hidden. You included Sheet5 in your array so I left it. If you are selecting sheets in order as one does for a group, then you don't have to list each sheet. Howsoever, keep in mind that no matter what order you put sheet names into the array, the pdf uses sheet index order.

Here is the code to get each visible property, set to visible, make pdf, restore visible state.

Sub Button3_Click()
Dim a, aa, i, pdf As String

a = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7")
pdf = Environ("temp") & "\a.pdf"

'Get sheet's visible property, and set to xlSheetVisible
aa = a
For i = 0 To UBound(a)
aa(i) = Worksheets(a(i)).Visible 'Errors if sheet a(i) does not exist.
Worksheets(a(i)).Visible = xlSheetVisible
Next i

Sheets(a).Select 'Make sheets grouped.
ActiveSheet.ExportAsFixedFormat xlTypePDF, pdf, xlQualityStandard, True, False, , , True

Sheets(1).Select 'ungroup sheets
'Restore sheet's visible state
For i = 0 To UBound(a)
Worksheets(a(i)).Visible = aa(i)
Next i
End Sub



As for alignment, your sheets have different margins (Sheet2) in Page Layout for one thing. Alignment is up to you.

[elsuji]

Thanks for your reply.

I have another query.

In sheet2 the following to be updated in merged place from Data sheet

We here by certify that the Batching Plant Model CP30 bearing Sl. No. BP1585 supplied by us to M/s. Sri Nandi Conmix., was calibrated on 04-02-2019 by our Service Engineer Mr. Senthil Kumar P in presence of Mr. Umesh (Partner - M/s. Sri Nandi Conmix), Mr. Sathish (Incharge - M/s. Sri Nandi Conmix) and the calibration details are enclosed herewith. We certify that all the parameters are well within the tolerance limit.


We here by certify that the Batching Plant Model (from Data!B5) bearing Sl. No. (from Data!B6) supplied by us to (from Data!B3)., was calibrated on (from Data!B1) by our Service Engineer Mr.(from Data!B15) in presence of (from Data!B16, C16, D16), (from Data!B17, C17, D17),(from Data!B18, C18, D18),(from Data!B19, C19, D19 ) and the calibration details are enclosed herewith. We certify that all the parameters are well within the tolerance limit.

If (from Data!B16, C16, D16), (from Data!B17, C17, D17), (from Data!B18, C18, D18), (from Data!B19, C19, D19) is blank then it wont update here.

Pls tell me how to write the formula for this

[Kenneth Hobs]

That sounds like a formula issue. Please post a new thread with that topic. e.g. "Concatenate Conditional Long String in Formula"

You can copy the link here and paste there if they need to reference this one for a file or such.

[elsuji]

Thanks for your reply. I had posted new thread for the above query

[elsuji]

I write another code.

Sheet4 (B11:G136), Sheet5 (B11:G36), Sheet6 (B11:G26), Sheet7 (B11:G26), Sheet8 (B11:G31) in all these range if the rows are in blank then the entire row should delete and the last row the border to be create.

For that i tried the below code

Sub DeleteEntireRow()
Dim lr As Long

lr = Range("B:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range("B" & lr).Resize(50).EntireRow.Delete
End Sub


Sub DataBorders()
Worksheets("Sheet8").Range("B11:G31").BorderAround _
ColorIndex:=1, Weight:=xlThick
End Sub

But this not fully completed any my requirement. Can you please help me for this

And pls refer my attachment

24897

[Kenneth Hobs]

Post #14 should be another thread as well.

We add threads to solve a problem. This is so that others with similar problems can get help. Subject lines for new threads should be descriptive.

Example threads:
1. Make One PDF File That Includes Multiple Sheets (this thread's title as I would do it)
a. Print multiple sheet as single pdf page (http://www.vbaexpress.com/forum/showthread.php?65781-Print-multiple-sheet-as-single-pdf-page) (this thread's title)
b. Print multiple sheets to a single pdf f (http://www.vbaexpress.com/forum/showthread.php?65781-Print-multiple-sheet-as-single-pdf-page)ile (this thread's title changed to reflect the goal)
2. Concatenate Conditional Long String in Formula (your post #11)
3. Delete Blank Rows and Add Borders on Last Row (your post #14)

I guess for (3), one would want to know which sheet needed that.

[elsuji]

Dear Kenneth,

I created code for Delete Blank Rows and Add Borders on Last Row.

Sub DeleteEntireRowAddBorder()
'For deleting the blank rows
Dim lr As Long
lr = Range("B:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range("B" & lr).Resize(50).EntireRow.Delete

'For add border at last row
Dim LastRow As Long
LastRow = Range("B" & rows.Count).End(xlUp).Row
Range("B" & LastRow, "G" & LastRow).Borders(xlEdgeBottom).Weight = xlMedium
End Sub


It is working as per my condition. But the thing this code to be apply for all sheet4, Sheet5, Sheet6, Sheet7, Sheet8.

Can you please guide me how to apply this cod for all the sheets

[paulked]

Elsuji, I would heed Ken's post #15 and start a new thread for this :thumb

[elsuji]

Where can I found that thread

[Kenneth Hobs]

You create it like this thread by click Post New Thread at: http://www.vbaexpress.com/forum/forumdisplay.php?17-Excel-Help

[elsuji]

Is it possible to print with and without header and footer

[Kenneth Hobs]

Set in Page Layout.

[elsuji]

I need code for print without header and footer

[Kenneth Hobs]

You can record a macro if you don't want to set in manually in Page Layout ribbon item, Print Titles, Header/Footer.