Help with Loops

I have a workbook, with 13 sheets, 1st one named Summary, and the rest ProdLine1 to 12 representing production lines. The ProdLines contains various information on orders going through production. I need to find a way in VB to loop though all the ProdLines and check if an Order in Column A contains the word "InProd". If it has, then copy the entire row to the Summary sheet., and then loop through to the next order with “InProd” and copy that row to the Summary sheet, an so on through all the ProdLines.

Due to my inexperience of VB, I have spent a great deal of time trying to find a solution to this problem, but I hope someone out there will come to my rescue. Thanking you all in advance.

Hi stevebriz
This is what I have so far.

Sub InProd()
Dim i As Integer
i = 2
Do Until IsEmpty(Cells(i, 1))
If Cells(i, 1).Value = "InProd" Then
Selection.Copy
Worksheets("Summary").Select
Worksheets("Summary").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

End If

i = i + 2
Loop

End Sub

Thanks
manoj

Hi stevebriz,
It works beautiful. A great big thank You.

Originally Posted by stevebriz

try this

Sub test2()
Dim SHTNM As String
Dim R As Long
R = 1 ' Summary Row to start paste of InProd rows from other sheets


For sht = 1 To 12 ' sheets ( 1 to 12) production sheets
SHTNM = "ProdLine" & sht
Sheets(SHTNM).Select
With Sheets(SHTNM).Range("A1", Range("A65536").End(xlUp).Address)

    Set c = .Find("InProd", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Sheets(SHTNM).Range(c.Address).EntireRow.Copy
            Sheets("Summary").Cells(R, 1).PasteSpecial xlPasteAll
           R = R + 1
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With


Next sht
Sheets("Summary").Select
End Sub

Hello Steve,

I came across your code, and I have found very useful, however how would I make a reference to sheets 1 to 12 if they are in a separate workbook, say on my c drive called " Data"?

Thanking you for your time
Regards
Odggi

Hi all,

also found this script really handy. But how would you adapt it for two conditions - e.g a specific date or range of dates in column b.


thanks for all the help as usual...