Copy a column from one tab to another using a column reference

[salih129]

Hi. I would like to copy a column using the column name instead of a column reference. With reference to the code below, I would like to change the Autofilter field:=70 to a autofilter:= (to the name of the column) i.e date/time etc. And I would like to change the range command to include the name of the column instead of the BR10 or so. Thanks

Sheets("Overall - Basestock Lifting").Activate 
    Range("BR10").Select 
   Selection.AutoFilter Field:=70, Criteria1:="1" 
   Range("BU10").Select 
   Selection.AutoFilter Field:=73, Criteria1:="TRUE" 
   Range(Cells(14, 2), Cells(i, 3)).Select    Selection.Copy 
   Sheets("Gp I Lifting").Select    Range("A5").Select 
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks:=False, Transpose:=False

[p45cal]

Could you attach a version of your workbook with this?
It's hard work to reproduce your scenario.

[salih129]

I would like to copy a column data with the header "date" from a worksheet name "Grades" and then copy into another column "Date" in another worksheet named "results". Both of these worksheets is in a same workbook. Is there a generic code to execute this? thanks.

[p45cal]

Could you attach a version of your workbook with this?

I'll take that as a no then.
try:

Sub blah()
With Sheets("Grades")
  SourceColumn = Application.Match("Date", .Rows(1), 0)
  Set SourceRange = Intersect(.UsedRange, .Columns(SourceColumn)).Offset(1)
End With
With Sheets("Results")
  DestinationColumn = Application.Match("Date", .Rows(1), 0)
  Set DestinationRange = .Cells(2, DestinationColumn) 'or if there's already something there:
  'Set DestinationRange = .Cells(.Rows.Count, DestinationColumn).End(xlUp).Offset(1)
End With
SourceRange.Copy DestinationRange
End Sub

[salih129]

Hi. Thanks a lot,p45cal. It works. However, how do i edit the code, if the column range that I want to select does not start from row 1 instead? And i just want to select the data downwards? For example, I want to copy the column data with the column name "date" but not including the "date", just the data in the following rows? Appreciate your help.

[Aussiebear]

Change the value in

SourceColumn = Application.Match("Date", .Rows(1), 0)