Macro to Print Update and Print Sheet

Hi there,

Let me know if the following does the trick - just change the reference to Sheet1 to the tab name where the invoice number(s) are listed and the references to Sheet2 to the invoice tab.

HTH

Robert

Sub PrintAllInvs()

Sheets("Sheet1").Select
Range("B12").Select

Do Until IsEmpty(ActiveCell) = True
ActiveCell.Copy Sheets("Sheet2").Range("N1")
Sheets("Sheet2").PrintOut
ActiveCell.Offset(1, 0).Select
Loop

End Sub

I tested it but a different way. Turns out I need to be able to print sometimes and other times just keep copies as different sheet tabs.

So, I set this up but... it only did the first one and stopped. Seems that the offset command affected cell n1 on the invoice sheet because cell n2 was the active cell on that page when the macro stopped. I don't know how to get the offset to work on the "CL lookup" page in the desired range (b12:down) Any Ideas???

Sub PrintAllInvs()

Sheets("CL lookup").Select
Range("B12").Select

Do Until IsEmpty(ActiveCell) = True
ActiveCell.Copy Sheets("Invoice").Range("N1")
Sheets("Invoice").Copy Before:=Sheets(5)
Sheets("Invoice (2)").Name = Range("I14").Value
'Sheets("Sheet2").PrintOut
ActiveCell.Offset(1, 0).Select
Loop

End Sub

This is 2nd priority, but it would be nice to somehow choose if I want to just print or If I want to have an active copy of each invoice. And if it is doable, if I choose active copy, then maybe an option at end to print active invoices.

Thank you very much for your help and advice.

I tested code and,
One weird thing is that I tested it with only having "C"s and it created them, then asked to print, when I hit yes to print it printed them fine, but also errored out highlighting this:

If StrConv(Range("N" & lngRowNo), vbUpperCase) = "C" Then

The only other issue with the code is when I only have "P", I then don't need the second part to run.
But it still works and is a time saver.

Hi there,

It's hard to fully test without actual data, but see if this meets all your needs.

Regards,

Robert

Sub PrintAllInvs()

Application.ScreenUpdating = False

Dim lngRowNo As Long
Dim intPrintCounter As Integer
Dim strActiveInv As String

lngRowNo = 12
intPrintCounter = 0

Sheets("CL lookup").Select
Range("B" & lngRowNo).Select

Do Until IsEmpty(ActiveCell) = True

    If StrConv(Range("C" & lngRowNo), vbUpperCase) = "C" Then
          
    ActiveCell.Copy Sheets("Invoice").Range("N1")
           
    Sheets("Invoice").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = Range("I14").Value
    Sheets("CL lookup").Select
    lngRowNo = lngRowNo + 1
    Range("C" & lngRowNo).Select
    
    Else
    
    ActiveCell.Copy Sheets("Invoice").Range("N1")
    Sheets("Invoice").PrintOut
    intPrintCounter = intPrintCounter + 1
    lngRowNo = lngRowNo + 1
    Range("B" & lngRowNo).Select
    
    End If
    
Loop

    If intPrintCounter = 0 Then Exit Sub
    
    If MsgBox("Would you like to print all the invoices?", vbYesNo + vbInformation, "Print Active Invoices Editor") = vbYes Then
    
    lngRowNo = 12
    Sheets("CL lookup").Select
    Range("B" & lngRowNo).Select
    
    Do Until IsEmpty(Range("B" & lngRowNo)) = True
    
        strActiveInv = Range("B" & lngRowNo)
        Sheets(strActiveInv).PrintOut
        lngRowNo = lngRowNo + 1
    
    Loop

    End If
    
Application.ScreenUpdating = True

End Sub