SOLVED

Loop a list based on condition and print

Brass Contributor

Hi all,

 

I have done this once, but I cannot remake the situation, hope someone can help me.

 

I need to print a number of reports with a specific execution date. (see file attached)

That date is found in cell B3 of worksheet "Werkbon"

 

On another worksheet (Planning) I have a list with all the workorders and the excecution date.

 

Now I need to fill in a workorder with that particular date of cell B3 (so the other fields in the report will LOOKUP), print that report, and fill in the next workorder with that particular date and print that report.

 

I know this is a loop function, but do not know where to start. Can anyone help me?

 

 

7 Replies
I made an extra step to ease up the process.

With a Index Small list I have made the list with workorders I want to print.

On worksheet Lijst, range A3:A40 I have the list.
I want to loop all the cells that <>""
(because I used the Index Small with IFERROR "")

@Celia9 

Please attach the updated workbook.

@Hans Vogelaar Here it is :)

best response confirmed by Celia9 (Brass Contributor)
Solution

@Celia9 

Thank you. Here is a macro:

Sub PrintReports()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet4")
    Set wt = Worksheets("Werkbon")
    r = 3
    Do While ws.Range("A" & r).Value <> ""
        wt.Range("B5").Value = "'" & ws.Range("A" & r).Value
        wt.PrintOut
        r = r + 1
    Loop
    Application.ScreenUpdating = True
End Sub
You are amazing Hans!
Hi Hans,

Soms heeft een cel in de "A" range een nul, soms is het ""
Hoe kan ik ze allebei uitsluiten? Dus alleen printen als het niet 0 en niet "" is?

@Celia9 

    Do While ws.Range("A" & r).Value <> "" And ws.Range("A" & r).Value <> 0
1 best response

Accepted Solutions
best response confirmed by Celia9 (Brass Contributor)
Solution

@Celia9 

Thank you. Here is a macro:

Sub PrintReports()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet4")
    Set wt = Worksheets("Werkbon")
    r = 3
    Do While ws.Range("A" & r).Value <> ""
        wt.Range("B5").Value = "'" & ws.Range("A" & r).Value
        wt.PrintOut
        r = r + 1
    Loop
    Application.ScreenUpdating = True
End Sub

View solution in original post