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

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

Code:

---

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

---

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

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.

Quote:

---

Originally Posted by p45cal

Could you attach a version of your workbook with this?

---

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

Code:

---

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

---

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.

Change the value in

Code:

---

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

---