Insert rows into worksheet based on criteria?

Microsoft Office excel 2007
July 12, 2011 at 07:55:32
Specs: Windows XP SP3
I have two workbooks, say:workbook_a.xlsx and workbook_b.xlsx and both contain different worksheets. Two of those worksheets are already linked by a couple of VLOOKUP formulas using a unique key which is the custid column. With this setup I can let users enter data in the workbook_a and then I can run all my BI on the workbook_b. Now, one of the most tedious things I do is search for new high risk customers in workbook_a so I can manually add them to workbook_b. I would like to do the following:
1. From workbook_a lookup custid in workbook_b.
2. If it exists, do nothing (I already added the customer and the VLOOKUP formula will take care of updating the cust_risk cell value).
3. If it doesn't exist, copy the row from workbook_a to workbook_b (maybe even not the whole row but just one cell?).

Is this possible? I'm using Excel 2007.

See More: Insert rows into worksheet based on criteria?

Report •

July 12, 2011 at 19:51:05
It can be done with VBA.

As an example, this macro will search for each cust_ID from [Book1.xlsm]Sheet1!D2:D12 in [Book2.xlsm]Sheet1!D:D.

If it doesn't find a cust_ID in [Book2.xlsm]Sheet1!D:D, it will copy the entire row from [Book1.xlsm]Sheet1 into [Book2.xlsm]Sheet1.

Obviously, the code would need to be modified to fit your workbook layout.

Sub GetCust_ID()
LastRw = Workbooks("Book2.xlsm").Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
 For Each cust_ID In Workbooks("Book1.xlsm").Sheets(1).Range("D2:D12")
  With Workbooks("Book2.xlsm").Sheets(1).Range("D2:D" & LastRw)
   Set c = .Find(cust_ID, lookat:=xlWhole)
    If c Is Nothing Then
       LastRw = LastRw + 1
        Workbooks("Book1.xlsm").Sheets(1).Range("D" & cust_ID.Row).EntireRow.Copy _
         Destination:=Workbooks("Book2.xlsm").Sheets(1).Range("A" & LastRw)
    End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 13, 2011 at 06:25:23
Thanks for the answer DerbyDad03, but VBA code is not embedded into the Excel file itself right? I mean, if I copy the file to another machine, will this still work? I was looking more to a formula based approach.

Report •

Related Solutions

Ask Question