VBA Code to PDF print with loop with specific count.

[mogheg]

Hello Friends,

I have 2 worksheets.

Sheet 1 has list of invoice details with invoice numbers in more than 2500+ rows.

Sheet 2 has Invoice Format. Based on Invoice numbers in cell no. 'T5', all Invoice details will be filled.

in Sheet 2, I wish to save each PDF print of these invoices separately with changes in invoice no. in cell 'T5' based on no. of rows in "Sheet 1" from cell 'B6'. Only positive invoice value in Cell 'T26' should be printed.

I have made below code for print

Sub Printpdf()
'
' Printpdf Macro
'
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\gmoghe\Documents\invoice\" & Range("T5").Value, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


End Sub

 

[mogheg]

I have made few changes in Code in same sheet but its not working.

Sub Macro2()
'
' Macro2 Macro
'
'
    Dim x As Integer
    NumRows = Range("B6", Range("B6").End(xlDown)).Rows.Count
    Range("B6").Select
    Do Until IsEmpty(ActiveCell)
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\gmoghe\Documents\" & Range("T5").Value, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
        ActiveCell.Offset(1, 0).Select
    Loop


End Sub

 

[Takae]

ColumnB of Sheet1 is Invoice Number? like B6=001,B7=002...?
Hope this helps.

Sub Macro2()
    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "C:\Users\gmoghe\Documents\"
    
    NumRows = Range("B6", Range("B6").End(xlDown).Rows).Rows.Count
    
    For i = 6 To NumRows
        InvN = Sheets("sheet1").cells(NumRows, 2).Value
        
        Sheets("Sheet2").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=path & InvN & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next
End Sub

 

[mogheg]

Thank you very much for your help, You are right in Sheet 3 (billing working data) like B6=001,B7=002...

I have Sheet 6 (invoice format). Only change I have made is each PDF should save as Invoice number from cell value T5 in Sheet 6.

But still getting Syntax error.

I have used below code

Sub Macro2()
    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "C:\Users\gmoghe\Documents\"
    
    NumRows = Range("B6", Range("B6").End(xlDown).Rows).Rows.Count
    
    For i = 6 To NumRows
        InvN = Sheets("Sheet 3").Cells(NumRows, 2).Value
    
        Sheets("Sheet 6").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\gmoghe\Documents\" & Range("T5").Value,
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next


End Sub

 

[Takae]

Does T5 of Sheet6 have a formula? It is changing every one loop?

InvN = Sheets("sheet3").Cells(NumRows, 2).Value
InvN has Invoice number, so I put it in Filename.

Sub Macro2()    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "C:\Users\gmoghe\Documents\"
    
    NumRows = Range("B6", Range("B6").End(xlDown).Rows).Rows.count
    
    For i = 6 To NumRows
        InvN = Sheets("sheet3").Cells(NumRows, 2).Value
        
        Sheets("Sheet6").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=path & InvN & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next
End Sub

 

[mogheg]

Thank you very much Takae for your Code.

Yes T5 of Sheet6 is link from InvN B6 of Sheet3 which change after every print.

I have used your code but still giving error

Runtime Error 9 - Subscript out of range.

InvN = Sheets("sheet3").Cells(NumRows, 2).Value

Sub Macro2()
    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "D:\LAVASA\"
    
    NumRows = Range("B6", Range("B6").End(xlDown).Rows).Rows.Count
    
    For i = 6 To NumRows
        InvN = Sheets("sheet3").Cells(NumRows, 2).Value
    
        Sheets("Sheet6").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="D:\LAVASA\" & InvN & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next


End Sub

 

[Takae]

The file does not have Sheet3.
Please chenge sheets("sheet3") to actual sheet name which has the List of invoice number.

Sub Macro2()    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "D:\LAVASA\"
    
    With Sheets("sheet3")
        NumRows = .Range("B6", .Range("B6").End(xlDown).Rows).Rows.count
        
        For i = 6 To NumRows
            InvN = .Cells(NumRows, 2).Value
        
            Sheets("Sheet6").ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="D:\LAVASA\" & InvN & ".pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        Next
End With


End Sub

 

[mogheg]

Hello,

Thank you now its working but with single print. Not coming to next loop.

Only one invoice printed. And whether sheet"Invoice format" cell "J5" will change with next invoice number for each print?

Sub Macro2()
    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "D:\LAVASA\"
    
    With Sheets("Billing Working data")
        NumRows = Range("B6", Range("B6").End(xlDown).Rows).Rows.Count
    
    For i = 6 To NumRows
        InvN = Sheets("Billing Working Data").Cells(NumRows, 2).Value
    
        Sheets("Invoice Format").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="D:\LAVASA\" & InvN & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next
    
End With


End Sub

 

[Takae]

Why you don't use the newest code?
Why it didn't loop? because You drop the "." from the my newest code.

Sub Macro2()Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "D:\LAVASA\"
    
    With Sheets("Billing Working data")
        NumRows = .Range("B6", .Range("B6").End(xlDown).Rows).Rows.count
        
        For i = 6 To NumRows
            InvN = .Cells(NumRows, 2).Value
        
            Sheets("Invoice Format").ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="D:\LAVASA\" & InvN & ".pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        Next
End With
End Sub

 

[mogheg]

Hello Friend,

Its my mistake, I missed it.
I have corrected it but still not working.

Sub Macro2()
    Dim NumRows As Long, i As Long, InvN As Variant
    Const path As String = "D:\LAVASA\"
    
    With Sheets("Billing Working data")
        NumRows = .Range("B6", .Range("B6").End(xlDown).Rows).Rows.Count
    
    For i = 6 To NumRows
        InvN = .Cells(NumRows, 2).Value
    
        Sheets("Invoice Format").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="D:\LAVASA\" & InvN & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next
End With
End Sub