A Macro That Matches then Copies Data

rwmill9716

Active Member
Joined
May 20, 2006
Messages
434
I need a macro that will:

1. On Sheet 1, Read Cell A8's value (here 3)
2. Match that value with the values in row 2 (here Col D)
3. Copy the Matched Column to Column A of another Sheet (Sheet2)

Here, I show that copy in Col G, but I need it copied onto another Sheet

Note, my real data set will have many more columns of data to choose from (and the columns of data will be much longer (~50 cells)

Thanks in advance for your help.

Ric



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">Data 1</td><td style="text-align: center;;">Data 2</td><td style="text-align: center;;">Data 3</td><td style="text-align: center;;">Data 4</td><td style="text-align: center;;"></td><td style="text-align: center;;">Data 3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Match Row</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Match</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
What is not specified is whether the will be more than one occurrence of the target value in row 2.
Code:
Sub matchNcopy()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, fAdr As String
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    Set fn = sh1.Range("B2", sh1.Cells(2, Columns.Count).End(xlToLeft)).Find(sh1.Range("A8").Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If sh2.Range("A1") = "" Then
                    fn.EntireColumn.Copy sh2.Range("A1")
                Else
                    fn.EntireColumn.Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
                End If
                Set fn = sh1.Range("B2", sh1.Cells(2, Columns.Count).End(xlToLeft)).FindNext(fn)
            Loop While fAdr <> fn.Address
        End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,839
Members
413,943
Latest member
Dhornsby21

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