Copy a column based on a cell and paste to another sheet in next open column

[jrake40]

Hello,

I would like to copy a specific column from sheet1 to the next open column in sheet2.

So I would like to say take cell A1 and find that in row 1 of sheet1 and copy that specific column to sheet2. In addition it is copying a value with a formula behind it when I paste it I would like it to just be the number value.

Here is what I have so far which will copy a specified range (I need it to do a lookup type function) and will paste exact ( I need it to paste as values).

Sub Copy_Single_Col()

Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range
Set SourceSht = ThisWorkbook.Sheets("Alert Daily Data")
Set TargetSht = ThisWorkbook.Sheets("Alert Daily Data 2")
Set SourceCells = SourceSht.Range("B2:B38")
SourceCol = TargetSht.Range("IV1").End(xlToLeft).Column + 1
TargetSht.Cells(1, SourceCol).Value = Format(Date, "DD/MM/YYYY")
SourceCells.Copy TargetSht.Cells(2, SourceCol)


End Sub

 

[JLGWhiz]

See if this works.

Sub copyCol()
Dim fn As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Alert Daily Data")
Set sh2 = Sheets("Alert Daily Data 2")
With sh1
    Set fn = .Range("B1", .Cells(1, Columns.Count).End(xlToLeft)).Find(.Range("A1").Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.EntireColumn.Copy
            sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
        End If
End With
End Sub