Using Column Name

[Lucky Lucy]

Hi,
How do I copy a column that is called "Product Line" into Column A? The
Product Line column is in a different column on different reports so I cannot
reference it by column letter and hope to reference it by column
heading/name.

Thank you!
Luce

 

[Nigel]

To copy an entire column try something like, you can reference it by column
letter if you fully reference it with the name of the sheet

The following copies the entire column D on sheet1 to column A on sheet2

Sheets("Sheet1").Columns("D").Copy
Destination:=Sheets("Sheet2").Range("A1")

 

[Lucky Lucy]

Thank you Nigel. The problem I have is that I cannot reference by column
letter, as that column letter will vary on different worksheets. What I would
like to do is reference by column heading/title, so I can use the same macro
on different worksheets.

Thanks,
Luce

 

[Nigel]

The following function when used will return the column number where Row1
has the text passed to the function. If it is not found then the function
returns zero.

Function FindHeader(sHeader As String)
' take the string sHeader and return column
' number for the first column with sHeader in row 1
' else returns a 0
Dim iCol As Integer, iColumns As Integer
FindHeader = 0
With ActiveSheet
iColumns = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To iColumns
If Trim(.Cells(1, iCol)) = Trim(sHeader) Then
FindHeader = iCol
Exit Function
End If
Next
End With
End Function

To use it put the function in a general module and then call it by using

myColumn = FindHeader("Product Line")

The variable myColumn will have the number of the column where "Product
Line" is in row1. The function uses the activesheet so it looks at the sheet
currently selected.

HTH


--

Regards,
Nigel
(e-mail address removed)