Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two ranges on different sheets and copy matching results to new worksheet
Hi All,
I would really appreciate some help with the following: 1. I have a workbook with two worksheets. 2. The first worksheet is called "Customer Complaints" and the second is called "Shipped". 3. I need code that will compare the values in column i on the "Complaints" worksheet against column D on the "Shipped" worksheet. 4. If matched records are found, a new worksheet should be created called "Matched". 5. The contents of the entire row of the matched records on the "Shipped" worksheet should them be copied from the "Shipped" worksheet and pasted into the "Matched" worksheet. 6. If no matches are found, the code should not copy any values or create a new worksheet. 7. Please note that there may be multiple rows that match the criteria in which case I will need the macro to copy all these lines and not only the first row that matched the criteria. 8. The size of the data on both of the "Customer Complaints" worksheet and the "Shipped" worksheets will change every day so the code should be able to adapt accordingly. I am using Excel 2002 on windows XP. Any assistance with the above would be greatly appreciated. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two ranges on different sheets and copy matching results to new worksheet
Try the following.
Hth, Merjet Sub CopyStuff() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim iEnd As Long Dim iRow As Long Dim rng1 As Range Dim rng2 As Range Set ws1 = Worksheets("Customer Complaints") Set ws2 = Worksheets("Shipped") iEnd = ws1.Range("I65536").End(xlUp).Row Set rng1 = ws1.Range("I1:I" & iEnd) iEnd = ws2.Range("D65536").End(xlUp).Row Set rng2 = ws2.Range("D1:D" & iEnd) For Each c2 In rng2 For Each c1 In rng1 If c1 = c2 Then iRow = iRow + 1 If iRow = 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = "Matched" End If c2.EntireRow.Copy _ Destination:=ActiveSheet.Range("A" & iRow) Exit For End If Next c1 Next c2 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two ranges on different sheets and copy matching res
And if Merjet's code does not do what you want, try this one. Worksheets(1)
is "Customer Complaints" and Worksheets(2) is "Shipped" Function SheetExists(SName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ThisWorkbook SheetExists = CBool(Len(wb.Sheets(SName).Name)) End Function Sub Mtch() Worksheets(1).Activate Dim CompRng, ShpdRng As Range LstRw = Worksheets(1).Cells(Rows.Count, 9).End(xlUp).Row Set CompRng = Worksheets(1).Range(Cells(1, 9), _ Cells(LstRw, 9)) Set ShpdRng = Worksheets(2).Range("D:D") For Each c In CompRng If Not c Is Nothing Then For Each s In ShpdRng If s = c Then If Not SheetExists("Matched") Then Set NewSheet = Worksheets. _ Add(After:=Sheets(Sheets.Count), _ Type:=xlWorksheet) NewSheet.Name = "Matched" End If Worksheets(2).Activate shRng = s.Address Range(shRng).EntireRow.Copy Worksheets(4).Activate If Range("$A$1") = "" Then ActiveSheet.Paste Else Range("$A$1").Activate Do Until ActiveCell = "" ActiveCell.Offset(1, 0).Activate Loop ActiveSheet.Paste End If Worksheets(1).Activate End If Next s End If Next c Application.CutCopyMode = False End Sub "merjet" wrote: Try the following. Hth, Merjet Sub CopyStuff() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim iEnd As Long Dim iRow As Long Dim rng1 As Range Dim rng2 As Range Set ws1 = Worksheets("Customer Complaints") Set ws2 = Worksheets("Shipped") iEnd = ws1.Range("I65536").End(xlUp).Row Set rng1 = ws1.Range("I1:I" & iEnd) iEnd = ws2.Range("D65536").End(xlUp).Row Set rng2 = ws2.Range("D1:D" & iEnd) For Each c2 In rng2 For Each c1 In rng1 If c1 = c2 Then iRow = iRow + 1 If iRow = 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = "Matched" End If c2.EntireRow.Copy _ Destination:=ActiveSheet.Range("A" & iRow) Exit For End If Next c1 Next c2 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two ranges on different sheets and copy matching results to new worksheet
Thank you both for taking the time to help me.
The code works perfectly! Cheers, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing two sheets og copy value | Excel Worksheet Functions | |||
Copy & paste ranges dependant on results from another calculation | Excel Programming | |||
Comparing 2 dynamic ranges for matching names | Excel Programming | |||
Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet | Excel Programming | |||
Comparing Worksheet ranges | Excel Programming |