Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows in 2 sheets and copying matching rows from sheet 1 t
I've got 2 sheets in the same workbook - A and B.
Sheet B is a new unvalidated list of customers, sheet A is the validated list of customers. Each sheet has a column with customer number. I need to go through all customer numbers in Sheet B, and for each customer number to go to Sheet A to see if there is a match. If there is a match, then copy the whole row of the matching number from sheet A, and paste it to the matching row of sheet B (it should be converted to uppercase before pasting). Can you help me with macro ? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows in 2 sheets and copying matching rows from sheet 1 t
You will need to change the sheet names or index numbers if
they are not, in fact, A and B. The sheet to be copied from should be the active sheet when the procedure runs. Sub cpynpstUC() Worksheets("A").Activate Dim rng As Range lstRw = Worksheets("B").Cells(Rows.Count, 1).End(xlUp).Row lstCol = Worksheets("A").Cells(2, Columns.Count).End(xlToLeft).Column Set bRng = Sheets("B").Range("$A$2:$A" & lstRw) 'Assumes Header Row Set aRng = Sheets("A").Range("$A$2:$A" & Cells(Rows.Count, 1).End(xlUp).Row) For Each c In bRng If Not c Is Nothing Then x = c.Address For Each i In aRng If Not i Is Nothing Then y = i.Address If c.Value = i.Value Then Range(y).EntireRow.Copy Sheets("B").Range(x) Set convRng = Sheets("B").Range(x & ":IV" & Range(x).Row) For Each rng In convRng If rng.HasFormula = False Then rng.Value = UCase(rng.Value) End If Next End If End If Next i End If Next c End Sub "fbagirov" wrote: I've got 2 sheets in the same workbook - A and B. Sheet B is a new unvalidated list of customers, sheet A is the validated list of customers. Each sheet has a column with customer number. I need to go through all customer numbers in Sheet B, and for each customer number to go to Sheet A to see if there is a match. If there is a match, then copy the whole row of the matching number from sheet A, and paste it to the matching row of sheet B (it should be converted to uppercase before pasting). Can you help me with macro ? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Columns in work sheets and copying both rows to new | Excel Discussion (Misc queries) | |||
copy matching value rows to other sheet | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
Help on Matching and Copying Values In Two Different Sheets | Excel Programming | |||
Matching data rows from two different sheets into a new a new one | Excel Programming |