extracting data from EXCEL 2007

I've been struggling with this for a few hours now and in need of help.
I am trying to move data between two workbooks.
There are a number of actions I have managed to accomplish, but I'm stuck on this piece of inserted code.
'insert a "1" immediately below products in each of the latest CEVA sheets
Dim cel As Range
For Each WKS In WKB2.Worksheets
    Set cel = WKS.Range("A:A").Find("if MPL", LookIn:=xlValues, LookAt:=xlPart)
    If Not cel Is Nothing Then
    cel = 1
    End If

'loop through sheets in CEVA workbook and transfer product codes, dates and quantities
Dim cel2 As Range
For Each WKS In WKB2.Worksheets
    Set cel2 = WKS.Range("A:A").Find("HUM", LookIn:=xlValues, LookAt:=xlPart).Offset(1)
    Do Until cel2 = 1
        WKB1.Sheets("CevaBase").Range("B1048576").End(xlUp).Offset(1).Value = WKB2.WKS.Range("B10")
        WKB1.Sheets("CevaBase").Range("A1048576").End(xlUp).Offset(1).Value = WKB2.WKS.Range("A16")
        WKB1.Sheets("CevaBase").Range("C1048576").End(xlUp).Offset(1).Value = WKB2.WKS.Range("E16")

Open in new window

The first snippet replaces some text with the value 1.
I did this to make the logic of the second snippet easier for me, and it works fine.
The second snippet is where it breaks down.
I need to copy some cell values from WKB2 to WKB1.
Once the values have been copied I want to delete the row and then repeat the process until cel2 = 1.
I thought of deleting the rows after getting the data because the number of items I need to copy, (rows), varies sheet to sheet between the "HUM" row and the new value of 1 which I added in the first snippet.
I also need to cycle through the number sheets of WKB2 which vary in number every week.
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
Stephen ByromConnect With a Mentor Warehouse/ShippingAuthor Commented:
I managed to work it out, 5 hours later!
I changed the aforementioned code (lines 10 to 20) to this
'loop through sheets in CEVA workbook and transfer product codes, dates and quantities
For Each WKS In WKB2.Worksheets
    With WKS
    Do While .Range("A16") <> 1
        WKB1.Sheets("Transfer").Range("B1048576").End(xlUp).Offset(1).Value = WKS.Range("B10")
        WKB1.Sheets("Transfer").Range("A1048576").End(xlUp).Offset(1).Value = WKS.Range("A16")
        WKB1.Sheets("Transfer").Range("C1048576").End(xlUp).Offset(1).Value = WKS.Range("E16")
    End With

Open in new window

Works a treat
Stephen ByromWarehouse/ShippingAuthor Commented:
Silly awarding points to myself.
No other way though
All Courses

From novice to tech pro — start learning today.