VB Script to Save Results to a single PDF Fila

[amkkhan]

this small VBA Chunk iterates from roll no 1 to last i(collecting data from sheet1 and getting results.
but unfortunately, it prints only the last result instead of all the iterated results.
All I want is just to export all the iterated results into a single pdf file on the said path.

Sub printPDF()
For n = 5 To 15
RollNo = Sheets("Sheet1").Cells(n, "A")
StudentName = Sheets("Sheet1").Cells(n, "C")
Sheets("Results").Cells(13, "M") = RollNo
Next
Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & RollNo & "-" & StudentName & ".pdf", , , False, , , False
End Sub

 

[amkkhan]

I am expecting script to print only pages that have data and it may ignore blank sheets.

Thanks @HaHoBe

In single pdf file obviously

 

[John_w]

this small VBA Chunk iterates from roll no 1 to last i(collecting data from sheet1 and getting results.
but unfortunately, it prints only the last result instead of all the iterated results.
All I want is just to export all the iterated results into a single pdf file on the said path.

Sub printPDF()
For n = 5 To 15
RollNo = Sheets("Sheet1").Cells(n, "A")
StudentName = Sheets("Sheet1").Cells(n, "C")
Sheets("Results").Cells(13, "M") = RollNo
Next
Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & RollNo & "-" & StudentName & ".pdf", , , False, , , False
End Sub

Returning to your OP, try this macro.

Public Sub Create_PDF()
    
    Dim PDFsheets As String
    Dim n As Long
    
    Application.ScreenUpdating = False
    
    PDFsheets = ""
    For n = 5 To 8
        Worksheets("Results").Range("M13").Value = Worksheets("Sheet1").Cells(n, "A").Value
        Worksheets("Results").Copy After:=Worksheets(Worksheets.Count)
        PDFsheets = PDFsheets & ActiveSheet.Name & ","
    Next
    
    Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
    
    MsgBox "Created " & ThisWorkbook.Path & "\All Results.pdf"
    
End Sub

 

[amkkhan]

@John_w Perfact
everything working perfactly as expected.
Is it possible that it may ignore Blank Sheets. Please

Thanks

 

[John_w]

Which are the blank sheets?

 

[amkkhan]

Which are the blank sheets?

I have a list for 50 entries maximum. Every class have different number of students and every class have different strength.
When it starts from Roll No 1 to n...
It creates single sheet of 50 pages out of which only 23 have data(as I told earlier. For example if strength is 23)
For example there are 23 students enrolled in class. Now I am getting single pdf file of 50 pages out of which only 23 are filled with data and rest 27 are blank.
I hope you understand

 

[HaHoBe]

Hi amkkhan,

use

For lngRow = 5 To Sheets("Award List").Range("C56").End(xlUp).Row

Holger

 

[John_w]

I have a list for 50 entries maximum. Every class have different number of students and every class have different strength.
When it starts from Roll No 1 to n...
It creates single sheet of 50 pages out of which only 23 have data(as I told earlier. For example if strength is 23)
For example there are 23 students enrolled in class. Now I am getting single pdf file of 50 pages out of which only 23 are filled with data and rest 27 are blank.
I hope you understand

Please upload a sample workbook (with dummy data if necessary) to a file sharing site and post the link here so I can see what is happening and I'll try to fix it.

 

[HaHoBe]

@John_w:

see #19

Holger

 

[John_w]

I have a list for 50 entries maximum. Every class have different number of students and every class have different strength.
When it starts from Roll No 1 to n...
It creates single sheet of 50 pages out of which only 23 have data(as I told earlier. For example if strength is 23)
For example there are 23 students enrolled in class. Now I am getting single pdf file of 50 pages out of which only 23 are filled with data and rest 27 are blank.

Looking at your uploaded workbook - thanks @HaHoBe.

You need to be more precise when talking about 'single sheet', 'pages' and 'blank'. With roll numbers 33 and higher, the "Result_Card" sheet isn't blank, because it still has all the row and column headings, etc., only the data cells are empty.

Try this slightly modified macro, which loops through rows 5 to 54 on the "Award List" sheet and if the column C cell (Name) isn't empty updates M13 on the "Result_Card" sheet to generate the result for that roll no. and copies the "Result_Card" sheet for later exporting to the single PDF file.

Public Sub Create_PDF()
  
    Dim PDFsheets As String
    Dim n As Long
  
    Application.ScreenUpdating = False
  
    PDFsheets = ""
    For n = 5 To 54
        If Not IsEmpty(Worksheets("Award List").Cells(n, "C").Value) Then
            Worksheets("Result_Card").Range("M13").Value = Worksheets("Award List").Cells(n, "A").Value
            Worksheets("Result_Card").Copy After:=Worksheets(Worksheets.Count)
            PDFsheets = PDFsheets & ActiveSheet.Name & ","
        End If
    Next
  
    Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select
  
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
  
    Application.ScreenUpdating = True
  
    MsgBox "Created " & ThisWorkbook.Path & "\All Results.pdf"
  
End Sub

 

[amkkhan]

Looking at your uploaded workbook - thanks @HaHoBe.

You need to be more precise when talking about 'single sheet', 'pages' and 'blank'. With roll numbers 33 and higher, the "Result_Card" sheet isn't blank, because it still has all the row and column headings, etc., only the data cells are empty.

Try this slightly modified macro, which loops through rows 5 to 54 on the "Award List" sheet and if the column C cell (Name) isn't empty updates M13 on the "Result_Card" sheet to generate the result for that roll no. and copies the "Result_Card" sheet for later exporting to the single PDF file.

Public Sub Create_PDF()
 
    Dim PDFsheets As String
    Dim n As Long
 
    Application.ScreenUpdating = False
 
    PDFsheets = ""
    For n = 5 To 54
        If Not IsEmpty(Worksheets("Award List").Cells(n, "C").Value) Then
            Worksheets("Result_Card").Range("M13").Value = Worksheets("Award List").Cells(n, "A").Value
            Worksheets("Result_Card").Copy After:=Worksheets(Worksheets.Count)
            PDFsheets = PDFsheets & ActiveSheet.Name & ","
        End If
    Next
 
    Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
 
    Application.ScreenUpdating = True
 
    MsgBox "Created " & ThisWorkbook.Path & "\All Results.pdf"
 
End Sub

Perfact . Everything is working as expected.
Bundle of Thanks to @HaHoBe and @John_w . Your help mean a lot to me
I am really really grateful to you.