Craig:

Below is some code that does something like you need. It copies the columns
in the order it finds them as it goes across row six. Obviously, you will
need to adjust the workbooks and worksheet names to your settings. This
should go without saying, but please backup your work prior to testing, as
the macro does save the target workbook during execution.


Public Sub CopyColumns()
Dim wkbBase As Workbook
Dim wkbTarget As Workbook
Dim wksBase As Worksheet
Dim wksTarget As Worksheet
Dim i As Integer

Set wkbBase = Workbooks("TestBase.xls")

Set wkbTarget = Workbooks.Open("H:\TestTarget.xls")
Set wksTarget = wkbTarget.Sheets("Sheet1")

wkbBase.Activate
Set wksBase = wkbBase.Sheets("Sheet3")
wksBase.Select
For i = 1 To wksBase.UsedRange.CurrentRegion.Columns.Count
wksBase.Cells(6, i).Select
If ActiveCell.Value = "Driver" Or ActiveCell.Value = "Payable" Or _
ActiveCell.Value = "Receivable" Or ActiveCell.Value =
"Total" Then
ActiveCell.EntireColumn.Copy
wkbTarget.Activate
wksTarget.Activate
wksTarget.Columns(wksTarget.UsedRange.CurrentRegio n.Columns.Count
+ 1).Select
wksTarget.Paste
Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
Selection.Delete xlShiftUp
wksTarget.Cells(1, 1).Select
wkbTarget.Save
wkbBase.Activate
End If
Next i

Set wkbBase = Nothing
Set wkbTarget = Nothing
Set wksBase = Nothing
Set wksTarget = Nothing
End Sub


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Craigm" wrote in
message ...

I need to copy columns based on column name in row 6 to the last empty
column in a different workbook.

The Excel sheet is created by a mainframe using the same file name each
time the job is run. The column names in row 6 never change. I need to
copy 4 columns every time the job is run to a consolidated sheet. The
columns are not beside each other (Columns D, AP, AR, L). The column
names are "Driver", "Payable", "Recievable" and "Total".

I need to place the copied columns in the first available empty column
in the consolidating sheet and delete the five rows above the copied
columns.

This is way beyond my knowledge. Can anyone throw this dog a bone to
learn from?

Perpetual THANKS in advance!

Craig


--
Craigm
------------------------------------------------------------------------
Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=380205