extracting data from EXCEL 2007

Hi,
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
Next

'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")
        cel2.EntireRow.Delete
    Loop
Next
    WKB2.Close

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.
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
 
Stephen ByromConnect With a Mentor Warehouse/ShippingAuthor Commented:
Hi,
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")
        WKS.Range("A16").EntireRow.Delete
    Loop
    End With
Next

Open in new window

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

From novice to tech pro — start learning today.