[Solved] How to convert multiple sheets to pdf using via

[sai]

Hi,

I having hard time writing a vba code with below specification.

1) I have 80 sheets, starting with name sheet 2 to sheet 81.
2) When sheet 2 merged cell D711 matches with sheet3 merged cells B7:B11, then print sheet2 and sheet3 as one pdf.
3) when sheet2 merged cells B7:B11 is matching with sheet3 merged cells B7:B11 then print sheet and pdf.
4) when sheet2 merged cells B7:b11 is not matching with sheet3 merged cells d7:d11 then move to sheet 3 and run the same process until sheet 81.

 

[Yousuf Shovon]

1) I have 80 sheets, starting with name sheet 2 to sheet 81.
2) When sheet 2 merged cell D711 matches with sheet3 merged cells B7:B11, then print sheet2 and sheet3 as one pdf.
3) when sheet2 merged cells B7:B11 is matching with sheet3 merged cells B7:B11 then print sheet and pdf.
4) when sheet2 merged cells B7:b11 is not matching with sheet3 merged cells d7:d11 then move to sheet 3 and run the same process until sheet 81.

Hello Sai,

Welcome to ExcelDemy Forum! Thank you for posting your concern here. Today, I will assist you in converting multiple sheets to PDF using VBA.

I understand you have specified 3 conditions in your post. I am going through them and have some questions about them for instance:

• What do you want to do when the first condition is met? Do you check through the second condition or end the loop?
• What If both conditions are met?
• And, what do you mean by "print sheet and pdf"? I guess you want the matching sheets printed separately.

Upon my understanding, I have found a way to convert multiple sheets to PDFs. Please, see the below VBA code.

Sub SheetstoPdf()

'ExcelDemy product

Dim Sheet1Name As String
Dim Sheet2Name As String

Sheet1Name = "Sheet2"
Sheet2Name = "Sheet3"

If Worksheets(Sheet1Name).Range("D7") = Worksheets(Sheet2Name).Range("B7") Then
    Sheets(Array(Sheet1Name, Sheet1Name)).Select
    Sheets(Sheet1Name).Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1Name & " And " & Sheet2Name, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If

If Worksheets(Sheet1Name).Range("B7") = Worksheets(Sheet2Name).Range("B7") Then

    Sheets(Sheet1Name).Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1Name, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Sheets(Sheet2Name).Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet2Name, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If

For i = 2 To 80
    matched = 0
    Sheet1Name = "Sheet" & i
    Sheet2Name = "Sheet" & Right(Str(i + 1), Len(Str(i + 1)) - 1)
    If Worksheets(Sheet1Name).Range("D7") = Worksheets(Sheet2Name).Range("B7") Then
        Sheets(Array(Sheet1Name, Sheet2Name)).Select
        Sheets(Sheet1Name).Activate
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1Name & " And " & Sheet2Name, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        matched = matched + 1
    End If
   
    If Worksheets(Sheet1Name).Range("B7") = Worksheets(Sheet2Name).Range("B7") Then
   
        Sheets(Sheet1Name).Activate
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1Name, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Sheets(Sheet2Name).Activate
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet2Name, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        matched = matched + 1
    End If
   
    If matched > 0 Then
        Exit For
    End If
   
Next i

End Sub

The code checks the following 3 conditions and prints PDFs based on the specifications:

• It checks if the value in cell D7 of Sheet2 matches the value in cell B7 of Sheet3. If the condition is met, it selects both sheets, it exports them as a combined PDF file with a name formed by concatenating the sheet names. For instance: Sheet2 And Sheet3.
• It checks if the value in cell B7 of Sheet2 matches the value in cell B7 of Sheet3. If the condition is met, it exports it as a PDF with the same name as the sheet separately.
• Lastly, If Sheet2 B7:B11 does not match with Sheet3 D7: D11 then move to Sheet3 and run the same process until Sheet81.

Note: Since you mentioned D7: D11 as a merged cell, we will specify the range as D7. Excel knows merged cells by their first cell name.

I am attaching the solved workbook and an output PDF here. Please, practice and implement this to your dataset. For further questions, please share a demo dataset and specify the criteria with a thorough explanation.

Regards,
Yousuf Shovon

 

[sai]

Getting an error message as "subscript out of range "If Worksheets(Sheet1Name).Range("D7") = Worksheets(Sheet2Name).Range("B7")

 

[sai]

and select method of sheets class failed in "Sheets(Array(Sheet1Name, Sheet1Name)).Select

 

[Yousuf Shovon]

Getting an error message as "subscript out of range "If Worksheets(Sheet1Name).Range("D7") = Worksheets(Sheet2Name).Range("B7")

Hello Sai,

Thank you for sharing your concern.
It is a bit confusing because the code runs as expected on my end. The Subscript out of range error can happen if we reference a sheet name that does not exist in the workbook. the error in this line suggests that Sheet2 and Sheet3 those we previously declared as Sheet1Name and Sheet2Name do not exist.

So, recheck your sheet names. Look if there are any spaces or characters in the sheet name. And, adjust them accordingly in the following lines:

Sheet1Name = "Sheet2"
Sheet2Name = "Sheet3"

For instance, if you have spaces in the sheet name, then write:

Sheet1Name = "Sheet 2"
Sheet2Name = "Sheet 3"

Nevertheless, if you face further issues with the code, please reach out to us again and share a demo file or the code you tried before for our understanding. Thank you again.

Regards,
Yousuf Shovon

 

[sai]

Sorry, for bugging you again. Thank you, for answering my questions.
The result what I am getting is only sheet2 and sheet3 in one pdf and stopping being produce other sheets into pdf by getting an error message as "Document is not saved. The document may be open or an error may have been encountered when saving" in the code
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1Name & " And " & Sheet2Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
I tried doing Openafterpublish = False, but nothing is working.


I am writing my specification again. Just for your reference.

1) when sheet2 D711 = sheet3 b7:b11, then convert sheet2 and sheet3 to one PDF and move to sheet4.
2) when sheet2 b7:b11 = sheet3 b7:b11, then print sheet2 and sheet3 as separate pdf.
3)when sheet2 B7:b11 is not equal to sheet3 d7:d11, then end and move to Sheet 3 and follow the step 1.

 

[Yousuf Shovon]

3)when sheet2 B7:b11 is not equal to sheet3 d7:d11, then end and move to Sheet 3 and follow the step 1.

Hello Sai,

Thank you for your feedback!

Can you please clarify this step again? You asked if Sheet2 B7: B11 is not equal to Sheet3 D7: D11, then end and move to Sheet 3 and follow step 1. But what if Sheet2 B7: B11 and Sheet3 D7: D11 are equal?

Based on your criteria, I have found some loopholes such as:

• It's not specified what to do if the conditions are not met initially in Step 1. Should we proceed to the next row or take some other action?
• The process could potentially create an infinite loop if the conditions are never met in Step 1, leading to Step 3 being repeatedly executed.

Please, correct us if we are missing something here and recheck the ranges. Also, share a demo file with the requirements again if possible. Thank you again.