VBA - copy coulmn with value from cell in row 1

[nutratroy]

This seems straightforward so I thought I'd be able to find it, but I've been searching for an hour now without any luck. I'm new to VBA so I appreciate any help!

Sheet2 has a bunch of data organized in columns, with each column identified by a unique number in Row 1. If I type a number in a cell (say A1) on Sheet1, I need a script that will copy the column that has that number in Row 1.

Long time lurker, first time poster. You guys are amazing, thanks in advance!

 

[JoeMo]

Care to share any thoughts you might have about what to do with the copy once its on the clipboard?

 

[JLGWhiz]

You were not clear on how you wanted to trigger the code to run, ie. manually, by button or event, so I made it manually. Copy the code to the standard code module 1. See below for access intstructions and how to run the code from Excel.

Sub findNCopy()
Dim sh1 As Worksheet, sh2 As Worksheet, f As Range
Set sh1 = Sheets(1) 'Replace the index numbers with sheet names enclosed in Quote marks.
Set sh2 = Sheets(2) 'same as above
    Set f = sh2.Range("1:1").Find(sh1.Range("A1").Value, , xlValues, xlWhole)
        If Not f Is Nothing Then
            f.EntireColumn.Copy sh1.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
        End If
End Sub

 

[jolivanes]

Here is my longwinded attempt.
Similar as previous code.

Sub Maybe()
    Dim a As Long, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    a = sh2.Rows(1).Find(sh1.Range("A1").Value, , , 1).Column

    With sh2
        .Range(.Cells(1, a), .Cells(.Cells(.Rows.Count, a).End(xlUp).Row, a)).Copy sh1.Range("A1")
    End With
End Sub