Insert rows into worksheet from another workbook?

Question

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.

Answer 1

Yes, you can make a VBA macro for this.

I'd create a button, and in the click event, first make sure workbook_b is open, then use the following code to copy the rows where custid is in workbook_a, but not in workbook_b (note that in the end you just end up with a copy workbook_a in workbook_b, so it'd be a lot simpler to just copy the whole data range over).

Dim i As Integer, workingCol1 As Integer, workingCol2 As Integer
Dim workingRange1 As Range, workingRange2 As Range

workingCol1 = WorksheetFunction.Match("custid", Sheets("Sheet1").UsedRange.Rows(1), 0)
Set workingRange1 = Sheets("Sheet1").Range("AllCustomers").Columns(workingCol1)

workingCol2 = WorksheetFunction.Match("custid", Sheets("Sheet2").UsedRange.Rows(1), 0)
Set workingRange2 = Sheets("Sheet2").Range("CriticalCustomers").Columns(workingCol2)

For i = 2 To workingRange1.Rows.Count
   If Not IsError(Application.Match(workingRange1.Cells(i, 1), workingRange2, 0)) Then
    workingRange1.Rows(i).EntireRow.Copy
    Sheets("Sheet2").UsedRange.Rows(Sheets("Sheet2").UsedRange.Rows.Count).Offset(1, 0).EntireRow.PasteSpecial (xlPasteValues)
  End If
Next i

If you really only need one cell copied over, then you could do it with a worksheet-function, but it'd be a little complicated.

Answer 2

If you're interested, another approach would be to first set up a dynamic named range in workbook_a, which covers the area where you permit users to enter data. Then, use that dynamic named range as the source for a pivot table in workbook_b.

Any changes you make to your data in workbook_a, including new customers, get picked up when you refresh the pivot table in workbook_b.

Note that both workbooks need to be open.

Specifically, when I played around with this, I named my dynamic range in workbook_a as "AllCustomerData", with the data starting in cell A1 on sheet1, and defined the formula for "AllCustomerData" as

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Next, for the pivot table source in workbook_b, I used

 workbook_a.xlsx!AllCustomerData

Then, when I built the pivot table, I chose the "Classic Pivot Table layout". I placed all my fields in the "RowLabels" section of the Pivot table Fields List box, in the order of my fields in the original workbook_a. You can, of course, order them as you desire -- or only select the field(s) you need.

When I tested this, I did see that if I deleted a customer, refreshed the pivot table, then re-used that deleted customer's CUSTID at the bottom of the source range, and again refreshed the pivot table, that the re-used CUSTID would show up in the pivot table not at the bottom, but in the original position before deletion. (I hope that was clear!) I don't know if your business rules permit re-use of CUSTIDs, but be aware of that if you do.

Hope this helps. The VBA approach of @Lance Roberts is real nice too. It has an advantage over this pivot table approach of not incurring all that "pivot table overhead".