extracting data from EXCEL 2007

Posted on 2012-09-09
Last Modified: 2012-09-09
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.
Question by:Stephen Byrom
    LVL 1

    Accepted Solution

    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
    LVL 1

    Author Closing Comment

    by:Stephen Byrom
    Silly awarding points to myself.
    No other way though

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now