Search for rows in one sheet and copy into another sheet based on customer id

[chitiksha]

Hi,

I need someone to help with excel sheets and I do not have much
experience with it.
I have two workbooks: 1. Workbook 1 and 2. Workbook 2

Both of these workbooks contain the same no of EXCEL sheets which is
1. Customer sheet, 2. Address sheet 3. Email sheet, 4. Phone sheet

Workbook 1: Before Process contains data before a certain cleansing
process was run
Workbook 2: After Process contains data after a certain cleansing
process was run

All of the worksheets in both the workbooks contain a common field in
cloumn 1 which is the customer id.

Hence Customer sheet contains:
Customer id First Name Last Name Suffix Age
1 John Doe Jr 23
2 Jane Smith 32

The Address sheet contains:
Customer id Addl1 City State
Zip Type
1 123Main LA CA
XXXX Home
1 456Bea LA CA
XXXX Work
2 789abc LA CA
XXXX Billing
2 234xyz LA CA
XXXX Work

Etc. The same data is present in the sheets in Workbook2 but just
cleaned out.

I have a worksheet# 3 which I want to copy with the rows from the two
workbooks which match
the provided customer id. Customer id is selected manually and put
into workbook3.

Thus, if customer id = 1 is plugged into workbook 3 all the rows from
workbows1&2 with customer id = 1 in column 1 shall be selected and
copied into workbook 3. Hence workbook 3 shall contain.

Customer
1 John Doe Jr 23
Address
1 123Main LA CA
XXXX Home
1 456Bea LA CA
XXXX Work
etc

Hence,
I want to populate the third workbook based on a customer id which is
selected manually.
The customer id is present in in all the sheets within the workbooks
1& 2 as column 1

Can someone provide a macros/way as to be able to
1. Search two workbooks based on customer id
2. Populate all the matched rows into workbook3

Thanks

 

[JLatham]

Well, you can try this - totally untested, and some things in the code
you'll have to change in the "user defined" area near the beginning of it.
To put the code into your workbook #3, open it and press [Alt]+[F11] and then
use Insert | Module to create a code module to contain it. Copy this code
and paste it into the module, make changes to workbook/worksheet names as
needed and give it a try. All 3 workbooks MUST be open at the same time for
it to work. Use Tools | Macro | Macros to run it - it will request the
customer ID from you, if you leave -1 in the input box or clear the input
box, it will abort.

As I said, totally untested - if you have problems, post here. If code
fails, choose [Debug] and see which line of code failed (it will be
highlighted). In worst case, you can contact me at (remove spaces) HelpFrom
@ jlathamsite.com - if you do, send some sample data for me to work with.

To do an early check to see if it has any hope of running, after you copy it
in and make your changes, choose [Debug] from the VB Editor menu and then
click Compile VBA Project - it should compile without error. If you get an
error indication, it means either your edit messed up something or the paste
itself may have split a line where it shouldn't have been split. If it
compiles without error, give it a test. Make sure and work from copies of
your 2 source workbooks just to be safe. This should be a non-destructive
process for those 2 workbooks, but if I made a typo I haven't noticed, it has
a slim chance of erasing data in the source book.


Sub CombineData()
'all 3 workbooks must be open before calling this routine

'begin user defined values: change as required
Const WB1name = "Workbook1.xls"
Const WB2name = "Workbook2.xls"
'to hold source sheet names
Dim WSList(1 To 4) As String
'sequence these in the order you want
'to bring in the data from WB1 and WB2
'change to actual name of the sheets
'must be the same in both source workbooks
WSList(1) = "Customer"
WSList(2) = "Address"
WSList(3) = "Email"
WSList(4) = "Phone"

Const destSheetName = "Sheet1" ' sheet in this workbook for copy
'end of user defined values

Dim customerID As Integer ' hold number user enters

' will be workbook WB1name
Dim WB1 As Workbook
' will be workbook WB2name
Dim WB2 As Workbook
' varies as we work through books WB1 & WB2
Dim srcSheet As Worksheet
' will be sheet destSheetName
Dim destSheet As Worksheet
' to build up addresses to assign to ranges
Dim anyAddress As String
'to isolate customer ID numbers for examination
Dim custIDRange As Range
' to copy from
Dim srcRange As Range
' to copy to
Dim destRange As Range
'loop counter for working through worksheets
Dim SLC As Integer
'to look at customer ID on other sheets
Dim testID As Range
'to find available row in this workbook
Dim lastRow As Long
'to find last used column in source data
Dim lastCol As Long

'get the customer ID number to retrieve data for
On Error Resume Next
customerID = InputBox$("Enter Customer ID", "Cust.ID#", -1)
If Err <> 0 Then
MsgBox "Invalid entry - must be a positive integer", _
vbOKOnly, "Quitting"
Err.Clear
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0 ' clear error trap
If customerID <= 0 Then
Exit Sub ' -1 is 'invalid'
End If

'this will fail if other workbooks aren't open
Set WB1 = Workbooks(WB1name)
Set WB2 = Workbooks(WB2name)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
'clear previous results?
destSheet.Cells.ClearContents
'go get data from WB1
For SLC = LBound(WSList) To UBound(WSList)
Set srcSheet = WB1.Worksheets(WSList(SLC))
anyAddress = "A1:A" & _
srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Set custIDRange = srcSheet.Range(anyAddress)
For Each testID In custIDRange
If testID = customerID Then
'have match on customer ID
'copy all cells used on the source row
lastCol = Cells(testID.Row, Range("IV" & _
testID.Row).End(xlToLeft).Column)
anyAddress = "A" & testID.Row & ":" & _
Cells(testID.Row, lastCol).Address
Set srcRange = srcSheet.Range(anyAddress)
'find where to put it in this workbook
lastRow = destSheet.Range("A" & _
Rows.Count).End(xlUp).Row + 1
anyAddress = "A" & lastRow & ":" & _
Cells(lastRow, lastCol).Address
Set destRange = destSheet.Range(anyAddress)
'do the copy
destRange.Value = srcRange.Value
End If
Next ' end of test of customer ID entries
Next ' end of SLC sheet loop for WB1
'move to Workbook 2 and get the data from it

For SLC = LBound(WSList) To UBound(WSList)
Set srcSheet = WB2.Worksheets(WSList(SLC))
anyAddress = "A1:A" & _
srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Set custIDRange = srcSheet.Range(anyAddress)
For Each testID In custIDRange
If testID = customerID Then
'have match on customer ID
'copy all cells used on the source row
lastCol = Cells(testID.Row, Range("IV" & _
testID.Row).End(xlToLeft).Column)
anyAddress = "A" & testID.Row & ":" & _
Cells(testID.Row, lastCol).Address
Set srcRange = srcSheet.Range(anyAddress)
'find where to put it in this workbook
lastRow = destSheet.Range("A" & _
Rows.Count).End(xlUp).Row + 1
anyAddress = "A" & lastRow & ":" & _
Cells(lastRow, lastCol).Address
Set destRange = destSheet.Range(anyAddress)
'do the copy
destRange.Value = srcRange.Value
End If
Next ' end of test of customer ID entries
Next ' end of SLC sheet loop for WB2
'cleanup: release resources back to the system
Set custIDRange = Nothing
Set destRange = Nothing
Set srcRange = Nothing
Set srcSheet = Nothing
Set destSheet = Nothing
Set WB1 = Nothing
Set WB2 = Nothing
End Sub