VBA: Find value of A1, Sht1 in Sht2, copy Sht2 row to Sht3. Repeat through Sht1 range.

melevad

New Member
Joined
Feb 8, 2006
Messages
14
Hello,
I've looked for code already posted but am not finding what I need exactly. I'm not that good with VBA so I hope someone can post some code I an run with.

I have sheets in a workbook:
Sheet1 has a range of values in column 1 (I.E. A1 through A1000).
Sheet2 has 1000s of rows of data (I.E. A1 through XX3000). Most, but not all, of the values in the range of Sheet1 will be found in column G in Sheet2.
Sheet3 is empty.

I am looking to:
  • Get value from A1, sheet1.
  • Find match of that value in column G of sheet2.
  • Copy the entire row where the match is found in sheet2 to row 1, sheet 3.
  • Repeat above for the range of values in Sheet1 (copying to the next incremented row in Sheet3) for each repetition.
  • If no match is found, create a new incremented row on Sheet3 anyway and place the value from Sheet1 in the column G cell.
Your help is greatly appreciated!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How about
VBA Code:
Sub melevad()
   Dim Ary As Variant
   Dim r As Long, NxtRw As Long
   Dim Dic As Object
   Dim Sht3 As Worksheet
   
   Set Sht3 = Sheets("Sheet3")
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = 1
   NxtRw = 1
   
   With Sheets("Sheet2")
      Ary = .Range("G1", .Range("G" & Rows.Count).End(xlUp)).Value2
   End With
   For r = 1 To UBound(Ary)
      Dic(Ary(r, 1)) = r
   Next r
   With Sheets("Sheet1")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With Sheets("Sheet2")
      For r = 1 To UBound(Ary)
         If Dic.Exists(Ary(r, 1)) Then
            .Rows(Dic(Ary(r, 1))).Copy Sht3.Range("A" & NxtRw)
         Else
            Sht3.Range("G" & NxtRw).Value = Ary(r, 1)
         End If
         NxtRw = NxtRw + 1
      Next r
   End With
End Sub
 
Upvote 0
Solution
Glad to help & thanks for the feedback.
 
Upvote 0
Hello and Happy Friday!

I have a similar task to the one I posted above (and had resolved by @Fluff, thanks) but the parameters are a little different this time and I'm hoping for assistance.


I have two sheets, SHT1 & SHT2, that have 1000+ rows. The rows in both sheets have varying numbers of cells populated- some rows have data out to column P others out to column X. The cells in column G of both sheets contain a value that is unique within that column on that sheet. Most or all of the unique values in SHT1 are also present in SHT2, but they appear on different row numbers. What I need to do is this:

1) Learn the unique value from cell G1<current row> in SHT1.

2) Search for that value in column G of SHT2.
* If there is a match, copy entire row to buffer then go to step 3)
* If there is not a match, go back to step 1) and increment to next row (I.E. G2<new current row>).

3) Insert a new row in SHT1 below the current row.

4) Paste the row from SHT2 to the new row created in SHT1.

5) Shade all the cells yellow in the newly created row in SHT1. (this is a "nice to have" optional step)

6) Repeat 1) - 5) incrementing down 1 row at a time in SHT1 until a blank F<n> cell is encountered, then end program.
 
Upvote 0
As this is a different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top