• 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.

A simple macro to print excel sheets

snjpverma

Member
I want to print 2nd, 3rd and 4th sheet of an excel worksheet in pdf format.
The sheet numbers given above are the Index Number of sheets.
The printed pdfs should get saved separately for each sheet in the same folder as the excel sheet.

Names of the pdf should be as follows:
1st sheet pdf = "Total Summary"
2nd sheet pdf = "value in C6 of sheet 2" & "Summary"
3rd sheet pdf = "value in C6 of sheet 3" & "Summary"

Example: if cell C6 of 2nd sheet contains name "John", then its pdf copy should be saved as "John Summary".

The question has been cross-posted here MrExcel
 
@vletm, thanks for highlighting the inconsistency in the question. I have rectified it now. Please review and advise.

I want to print 2nd, 3rd and 4th sheet of an excel worksheet in pdf format.

Names of the pdf should be as follows:
2nd sheet pdf = "Total Summary"
3rd sheet pdf = "value in C6 of sheet 2" & "Summary"
4th sheet pdf = "value in C6 of sheet 3" & "Summary"

Example: if cell C6 of 2nd sheet contains name "John", then its pdf copy should be saved as "John Summary".
 
snjpverma
Almost as You've rectified it ...
Code:
Sub Do_It()
    APS = Application.PathSeparator
    pdf_path = ThisWorkbook.Path & APS
    For sh = 2 To 4
        Err.Clear
        pdf_name = "Summary Total"
        If sh > 2 Then pdf_name = "Summary " & Sheets(sh - 1).Range("C6")
        pdf_name = pdf_path & APS & pdf_name
        Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdf_name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        If Err.Number <> 0 Then MsgBox Err.Description
    Next sh
End Sub
 
@vletm

Thank you for your help.

Provide Code is getting stuck at the 10th line.

The sheet named "Summary Total" gets created properly, but after that the code gets stuck at the below line highlighted in bold.
The Error pop-up is this: Run-time error '438': Object doesn't support this property or method.

Issue with the below line in bold.
If Err.Number <> 0 Then MsgBox Err.Description

Please help on this as I know very little about VBA.
 
@vletm

I made the below mentioned two changes in the code and it works fine now.

Firstly, removed this line completely
If Err.Number <> 0[B] [/B]Then MsgBox Err.Description

Secondly, changed this
If sh > 2 Then pdf_name = "Summary " & Sheets(sh - 1).Range("C6")

To this
If sh > 2 Then pdf_name = "Summary " & Sheets(sh).Range("C6")


Additionally, I am sorry to say I forgot to mention that the 3rd and 4th sheet should only get printed if the Cell D97 in them have a value of more than 0 or else printing them is not required. Could you please add this condition to the code?

Thank you for your help.
 
snjpverma
Of course You can modify that code as You would like...

#1 That Err.number line needs to be there. ... but it needs to be as I've written
#2 You've rectified it to use as I've coded.
#3 You seems to forget as well as I wrote with my previous reply's the last line.
 
Thank you for your response Vletm
#1 That Err.number line needs to be there. ... but it needs to be as I've written
The reason why I removed If Err.Number <> 0[B] [/B]Then MsgBox Err.Description is because after Adding On Error Resume Next at the beginning of the code got me through the first part of this code line, but got stuck in the later part i.e. Then MsgBox Err.Description part. How can I use On Error Resume Next to get through both the parts of If--then.

#2 You've rectified it to use as I've coded.
Absolutely, no doubt about it. Just informed you about the changes so that you can alert me if there is any Caveat to the change I made.

#3 You seems to forget as well as I wrote with my previous reply's the last line.
I didn't get this sentence. Could you please rephrase it?
 
The 3rd and 4th sheet should only get printed if the Cell D97 in them have a value of more than 0 or else printing them is not required. Could you please add this condition to the code?

I managed to work this out by writing this code line
If Sheets(sh + 1).Range("D97").Value < 1 Then Exit Sub
before this code line
Next sh

Is this fine?

Thank You for your help.
 
snjpverma
#1 Can You see differences
My code If Err.Number <> 0 Then MsgBox Err.Description
Your code If Err.Number <> 0[B] [/B]Then MsgBox Err.Description
#2 You've rectified to use previous sheets cell C6 -value
#3 Upload a sample Excel-file which shows exact Your needs.
#4 About Your the latest question ... I won't use that Your given code.
 
#1 If Err.Number <> 0 Then MsgBox Err.Description - the red part is not a part of my code, it got added over here automatically because I had marked that part as bold. I think it is HTML code for bold which gets added when you put it inside a Inline Code.
#2 I have rectified it to use current sheets' C6 value, by removing "minus 1". And it is working perfect.
#3 Unfortunately, I am unable to upload the workbook here as it is on the official platform with confidential information. But uploading a sample worksheet that i just created for sample purpose.

I wanted to know what exactly is the purpose of this line in this script. Could you throw some light on this?
If Err.Number <> 0 Then MsgBox Err.Description
 

Attachments

  • print sheets macro.xlsm
    22.1 KB · Views: 4
snjpverma
#1 It was about Your given text which cannot work - that's why a sample file.
#2 Based Your If not, please let me know. ... and You skipped those detals.
#3 I've always asked a sample Excel-file from #8 reply
- What would others than You do with real data?
- That Your sample and real file could be safer - against users 'ideas'.
- - The sheet numbers given above are the Index Number of sheets.
#4 Purpose - to let user know that there has been error for some reason.

Code:
Sub Do_It()
    On Error Resume Next
    APS = Application.PathSeparator
    pdf_path = ThisWorkbook.Path & APS
    For sh = 2 To 4
        Err.Clear
        If sh = 2 Or (sh > 2 And Sheets(sh).Range("D97") > 0) Then
            pdf_name = "Summary Total"
            If sh > 2 Then pdf_name = "Summary " & Sheets(sh).Range("C6")
            pdf_name = pdf_path & APS & pdf_name
            Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdf_name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
        If Err.Number <> 0 Then MsgBox Err.Description, vbInformation, "Error"
    Next sh
End Sub
 
What would others than You do with real data?
I generally follow the practice of not posting confidential data of my client/employer which are not my own on any public forum. You might never know who will misuse your data and in what ways. So, better safe than sorry.

Nevertheless, thank you for the code.
 
Sheets(sh).ExportAsFixedFormat
Hello experts, the sheets(2) gets printed perfectly but I am get this error on the above code line while printing/creating pdf for the sebsequent sheets. Error is this: "Document not saved. The file may be open or an error may have been encountered while saving."
Please advise
 
Back
Top