PDA

View Full Version : Copy a column from one tab to another using a column reference



salih129
10-08-2013, 10:21 PM
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. ThanksSheets("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
10-09-2013, 01:04 AM
Could you attach a version of your workbook with this?
It's hard work to reproduce your scenario.

salih129
10-11-2013, 12:05 AM
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
10-11-2013, 05:20 AM
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
10-13-2013, 06:49 PM
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
10-13-2013, 11:44 PM
Change the value in
SourceColumn = Application.Match("Date", .Rows(1), 0)