COMPARE CELLS AND WRITE TO A NEW WORKBOOK

msavage

New Member
Joined
Sep 24, 2006
Messages
5
I have 2 workbooks in one spreadsheet - W1 and W2. both have A FIELD iWOSH TO COMPARE. I want to compare that field and if there is an exact match then write a third workbook ONLY with all the data from W1 into the 3rd workbook..make sense..The formula is driving me crazy
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello msavage, welcome to the board.
No, I'm afraid your question does not make sense. (To me anyway.)
As I understand it, you have two worksheets in one workbook, named W1 & W2.
You want to compare (what, some range/column?) in these two sheets and then copy
(what, just the matches?) to a third worksheet in the same workbook?

What range do you want to compare?
Does this third sheet already exist? (What's it's name?)

This will be easy enough to do once your question is understood.
 

msavage

New Member
Joined
Sep 24, 2006
Messages
5
COMPARE 2WORKSHEETS AND THEN WRITE TO A 3RD WORKSHEET

I have two worksheets in one workbook, named W1 with alot of data I need & W2 with just a bit of data. W2 tells me which are the specifics that I have to pull out of the 1st worksheet to get the right subset of the 1st worksheet and placeitin a claen workbook call w3. So Iwant to compare then emp numbers columns in both and when i get a hit then take all the data tahe was in the record in w1 and put all that data in w3. So it seems that I need to compare the entire ranges of both work sheets .Ijust creat w3as a blank worksheet in the same workbook. Make sense?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yes, so far it makes sense.
What range in W1 and W2 do you want to compare with each other?
What range from W1 do you want to copy to W3 when there is a match found?
 

msavage

New Member
Joined
Sep 24, 2006
Messages
5

ADVERTISEMENT

I only need to compare one column (d2 thru d964 in w1)and column (e2 thru 858 in w2)
range is a1 thru r 964 of w1 is what I would like to copy into the w3 worksheet ( or actually however many matches I get)..is that what you mean?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yes, perfect.
Give this code a try and see if I fully understand what you're after.
(Assumes you already have the sheet named W3)
Code:
Sub Demo()
Dim c As Range, List As Object, Rw As Long
Set List = CreateObject("Scripting.Dictionary")

With Sheets("W2")
  For Each c In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    If Not List.Exists(c.Value) Then
      List.Add c.Value, Nothing
    End If
  Next
End With

With Sheets("W1")
  For Rw = 2 To .Range("D" & Rows.Count).End(xlUp).Row
    If List.Exists(.Cells(Rw, "D").Value) Then
      .Rows(Rw).Copy Sheets("W3").Range("A" & Rows.Count).End(xlUp)(2)
    End If
  Next
End With

Set List = Nothing

End Sub
 

Forum statistics

Threads
1,136,697
Messages
5,677,270
Members
419,683
Latest member
MrVBAConfused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top