title: Trying to identify matching cells randomly distributed in 2 seperate Excel sheets, and have the matching data copied and pasted into a third sheet
body: <p>So I have several worksheets on the same Excel workbook that I need to compare. Worksheet 1 is the masterlist, and I need to compare worksheets 1-2, 1-3, 1-4. I then need to paste any similar 1-2 data cells in column A of worksheet 5, and similar 1-3 data cells in column B of worksheet 5, and 1-4 similarities to column C of worksheet 5. For starters I have focused on getting the 1-2 comparison to work. So far I have been able to get my test number to be pasted to cell A1 of sheet 5. I am running into trouble because it only works for 1 cell, and I cannot get the program to paste a similarity in A1, and then A2... etc, when I have multiple similar items. They just overwrite each other in cell A1, or in the entire A column. I am also running into trouble because the program as it is written stops when it hits a blank space, but I need it to just skip the blanks and read the next cell when it comes across them. This is because my data sheets are very messy and the data is scattered over several thousands of rows among several different columns, with spaces randomly interjected. Below is my working code for just reading a similarity, and pasting it into A1. I should note that I have considered adding a specific cell range depending on which sheet I am on in order to put an end point on the program, but I haven't quite figured out how to work it in.</p>
  
  <pre><code>Sub findDuplicates()
  ' code to find duplicates in 2 different worksheets
  
  Dim rng1, rng2, rngA, cell1, cell2 As Range
  ' 4 ranges have been defined
  
  Set rng1 = Sheets("Sheet1").Range("C:C")
  'rng1 defines the existing data in column C and worksheet1
  
  Set rng2 = Sheets("Sheet2").Range("C:C")
  'rng2 defines the data in column C and worksheet2
  
  Set rngA = Sheets("Sheet5").Range("A1")
  
  For Each cell1 In rng1
      If IsEmpty(cell1.Value) Then Exit For
      'check for empty rows. If true then exit the program
  
      For Each cell2 In rng2
          If IsEmpty(cell2.Value) Then Exit For
  
          If cell1.Value = cell2.Value Then
              'compare data in cell1 and cell2 and then copy/paste if they have equal values
              cell1.Copy
              Sheets("Sheet5").Select
              rngA.Select
              ActiveSheet.Paste
          End If
          'run the looping process
      Next cell2
  Next cell1
  
  End Sub
  </code></pre>
  
  <p>The general idea of what I imagine the program to look like would be something like</p>
  
  <pre><code>Define ranges
  
  Block of code that runs through each cell in sheet 1 comparing it to all cells in sheet 2.
  
  Block of code that, when similarities are found, copy/paste that cell on sheet 1 to sheet 5 column A
  
  *Program resumes scan from the next cell on sheet 1*
  
  Block of code that breaks the program when it hits the end of the specified cell range
  </code></pre>
  
  <p>Any help with this would be greatly appreciated! You would be saving me at least a week's worth of mindless work. </p>