get letter of the active column

[alienscript]

Hi VBA gurus,

Can someone help me please? I am stuck with this but temporarily use hardcode letter "F" of the column of the selected range. Any help is very very much appreciated. Thanks a million!

Dim finalRow As Long
finalRow = Sheets(2).Range("A65536").End(xlUp).Row
Rows("1:2").Select
    Selection.Find(What:="PartNumber").Activate
    Selection.Copy
    Sheets(2).Range(ActiveCell.Address & ":F" & finalRow).Copy _      
              Sheets(1).Range("IV1").End(xlToLeft).Offset(0, 1)

[Bob Phillips]

[vba]

Sheets(2).Range(ActiveCell.Address, Sheets(2).Cells(finalRow, Activecell.Column)).Copy _
Sheets(1).Range("IV1").End(xlToLeft).Offset(0, 1)
[/vba]

[Oorang]

Try This:
[VBA]Public Sub Test()
VBA.MsgBox ColumnLetter(ActiveCell.Column)
End Sub
Public Function ColumnLetter(ColumnNumber As Long) As String
Const lngOffset_c As Long = 64
ColumnLetter = VBA.Chr$(ColumnNumber + lngOffset_c)
End Function[/VBA]
Or you could switch from "range" to the "cells" syntax which allows you to use numeric row/column reference. (My own preference.)
[VBA]Public Sub Test()
Dim ws As Excel.Worksheet
Set ws = Excel.ActiveSheet
ws.Cells(1, 2).Value = "Foo"
End Sub[/VBA]

[Bob Phillips]

Perhaps better

[vba]

Dim finalRow As Long
With Sheets(2)
finalRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Rows("1:2").Find(What:="PartNumber").Activate
Selection.Copy
.Range(ActiveCell.Address).Resize(finalRow - ActiveCell.Row + 1).Copy _
Sheets(1).Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
End With
[/vba]

[Bob Phillips]

Try This:
[VBA]Public Sub Test()
VBA.MsgBox ColumnLetter(ActiveCell.Column)
End Sub
Public Function ColumnLetter(ColumnNumber As Long) As String
Const lngOffset_c As Long = 64
ColumnLetter = VBA.Chr$(ColumnNumber + lngOffset_c)
End Function[/VBA]

[vba]

Public Sub Test()
VBA.MsgBox ColumnLetter(Range("AA1").Column)
End Sub
[/vba]

[alienscript]

wow! you guys'r gorgeous. Thanks so much.