title: Excel VBA macro - how to combine 2 sheets the most efficient way
body: <p>Currently I have three sheets. Two of them contain data to be written into the third one.</p>
  
  <p>Sheet A has the following structure:</p>
  
  <pre>
  |    A    |   B  |  C |  D |  E |   F   |
  | Version | Name | XX | XY | XZ | DATA  |
  |   5.0   | Test | XX | XY | XZ | test1 |
  |   5.1   | Tree | XX | XY | XZ |  100  |
  |   5.1   | Test | XX | XY | XZ | test  |
  |   5.0   | Tree | XX | XY | XZ |  50   |
  ...
  </pre>
  
  <p>Sheet B has this:</p>
  
  <pre>
  |    A    |   B  |  C |  D |  E   |
  | Version | Name | XX | XY | DATA |
  |   5.1   | Test | XX | XY | test |
  |   5.0   | tree | XX | XY | 300  |
  |   5.1   | tree | XX | XY | 400  |
  |   5.0   | Test | XX | XY | test |
  ...
  </pre>
  
  <p>And Sheet C has this:</p>
  
  <pre>
  |   A  |  B |       C      |       D      |  E |       F      |      G       | H  | ..
  | Name | XX | SheetA - 5.0 | SheetB - 5.0 | XX | SheetA - 5.1 | SheetB - 5.1 | XX | ..
  ...
  </pre>
  
  <p>XX/XY/XZ = random data. Versions are strings and from 5.0 - 5.9. (actual the versions are years, but I think its more simple this way)</p>
  
  <hr>
  
  <p><br></p>
  
  <p>Now I want to combine Sheet A and Sheet B into Sheet C. It should look like this:</p>
  
  <pre>
  |   A  |  B |       C      |       D      |  E |       F      |      G       | H  | ..
  | Name | XX | SheetA - 5.0 | SheetB - 5.0 | XX | SheetA - 5.1 | SheetB - 5.1 | XX | ..
  | test | XX |    test1     |     test     | XX |     test     |     test     | XX | ..
  | Tree | XX |      50      |      300     | XX |      100     |      400     | XX | ..
  …
  </pre>
  
  <p>Currently I just take Sheet A and put it in the Right spot in Sheet C.<br>
  This doesn't take too much time but now I want to get Sheet B also into Sheet C so my current solution is to loop each row from Sheet B, find the row with the same name in Column "A" and fill the gaps.<br>
  The problem is, that each Sheet has 150k+ rows and this takes way too much time. And also there are names in Sheet B that aren't in Sheet C so the ".find" function search the whole 150k+ rows, the speed of this is like ~3 rows per second. Is there a quicker way?</p>
  
  <p>Code example how I fill Sheet B into Sheet C: (Code from A to C is nearly the same)</p>
  
  <pre><code>For Each Row In DataFromSheetB.Rows
      With Selection
          Set FindRow = Sheets("C").Range("A:A").Find(What:=Row.Cells(2), LookIn:=xlValues, LookAt:=xlWhole)
      End With
      If FindRow Is Nothing Then
          GoTo Skip2
      Else
          Select Case Row.Cells(1)
          Case "5.0"
              FindRow.EntireRow.Cells(4).Value = Row.Cells(5)
          Case "5.1"
              FindRow.EntireRow.Cells(7).Value = Row.Cells(5)
          Case "5.2"
              FindRow.EntireRow.Cells(10).Value = Row.Cells(5)
          Case "5.3"
              FindRow.EntireRow.Cells(13).Value = Row.Cells(5)
          Case "5.4"
              FindRow.EntireRow.Cells(16).Value = Row.Cells(5)
          Case "5.5"
              FindRow.EntireRow.Cells(19).Value = Row.Cells(5)
          Case "5.6"
              FindRow.EntireRow.Cells(21).Value = Row.Cells(5)
          Case "5.7"
              FindRow.EntireRow.Cells(24).Value = Row.Cells(5)
          Case "5.8"
              FindRow.EntireRow.Cells(27).Value = Row.Cells(5)
          Case "5.9"
              FindRow.EntireRow.Cells(30).Value = Row.Cells(5)
          End Select
      End If
  Skip2:
  Next Row
  </code></pre>