asked on

Locate and copy excel data from one work book into new work book based on specific criteria.

Hello everyone,

I am looking for some assistance creating a custom cut and paste macro for Excel.

I currently have a spreadsheet with columns ranging "A:BZ: I need to examine two fields in each row, begining on Row 3. The spreadsheet being examined is 'VIEWFMT" and will always have this title.

I need to look at each PART NUMBER in Column "BF" and the corresponding QUANTITY in Column "BM". Using row 3 as an example, If the quantity in "BM3" is greater then 0, then I want to copy "BF3" and "BM3" to a New workbook (Sheet1), "A1:B1". I want to loop through the list of 600 lines until complete.

I apprecaite any help that is available.

MC

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MCaliebe

ASKER

You are right. I should check "BF" as well to make certain it exists. I had to make a couple of changes to get it to work. My only issue is with rng2.value >0. My cell is a calculation so I don't kow if the code is not seeing the results as 0. I know if I change the code to test rng2.value <> "",. it will transfer everything.

The only thing I need after I correct this problem is to create a new workbook and transfer this information to the new workbook rather then to a sheet in the existing work book.

Sub Macro12()
'
' Macro12 Macro

Dim rng As Range
Dim rng2 As Range
Dim rngdst As Range


    Set rng = Range("BF3")
    Set rng2 = Range("BM3")
    

    Set rngdst = Worksheets("Sheet1").Range("A1")

    While rng.Value <> ""
        If rng2.Value <> "" Then

            Range(rng.Address & ", BM" & rng.Row).Copy rngdst
            Set rngdst = rngdst.Offset(1)

        End If

        Set rng = rng.Offset(1)
    Wend
    
    MsgBox ("Transfer complete")

End Sub

Open in new window

Norie

The value in the cell should make no difference if it's typed or 0.

I don't know what data you are working with, but there's a slight possibility there could be some accuracy problems.

That really depends on what calculations you are doing and the data.

Another possibility is that you have cells populated to say 2 decimal places whereas the actual value is 0.001.

As for the new workbook, how about just copying the sheet with the results.

Worksheets("Sheet1").Copy

Open in new window

This will create a new workbook with just Sheet1 in it.

Since it's a copy, the original Sheet1 will still be in the original workbook.

If you don't want that you can create the new workbook at the beginning and copy to that.

The tricky thing there is making sure the code refers to the correct workbooks/sheets.