Copy columns to free space in diff wrkbk

[Craigm]

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

 

[David Lloyd]

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.CurrentRegion.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.


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]

Row 1 (in the source worksheet TestBase.xls) must be populated for thi
macro to copy columns to work. If row 1 is blank the copy works but i
overwites all data in the target worksheet (TestTarget). If anything i
present in Row 1 the macro works perfectly.

I need to run across ROW 6 to determine the last used row. Rows
through 5 may or may not be empty.

My poor brain is totally failing me.

How do I confine the determination to copy a column to Row 6?
:confused

 

[David Lloyd]

Craig:

The source worksheet that I used to test this macro has no values in Rows 1
through 5. The line: wksBase.Cells(6, i).Select is specifically looking at
the values in Row 6 in the source workbook.

Regarding the Target workbook, you did not give much information regarding
its structure, so I had to make some assumptions. Since you said you wanted
to deleted Rows 1-5 in the Target, I assumed that was to bring these new
columns in line with other columns in the target workbook. The line:
wksTarget.Cells(1, 1).Select sets the cursor in Row 1 in the target. If for
some reason there is no data in the target workbook on this row, you can
change this value to choose another row. My example was meant as a guide,
as there was not enough information to provide a complete solution. You
will have to make adjustments to the code to meet your requirements. This
was only an example.

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

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


message
Row 1 (in the source worksheet TestBase.xls) must be populated for this
macro to copy columns to work. If row 1 is blank the copy works but it
overwites all data in the target worksheet (TestTarget). If anything is
present in Row 1 the macro works perfectly.

I need to run across ROW 6 to determine the last used row. Rows 1
through 5 may or may not be empty.

My poor brain is totally failing me.

How do I confine the determination to copy a column to Row 6?

 

[Craigm]

With your latest suggestions the clouds are starting to clear. I a
able to make this work now.

I am still curious as to why in the TestTarget worksheet if column A i
empty the copy works but keeps writing over itself in column B.
believe it has something to do with the way the CurrentRegion propert
works. Not having any rows or columns to use to detect a boundary are
I believe it is not able to increment past the row it has just written
In the current configuration I would need to save TestTarget durin
each loop. This would be too time consuming.

Your assitance has started me on the road to success. I have tried t
pull each line apart and understand what it is doing. My understandin
if VBA is growing. Thanks for investing your time in me.

Thank You for your kindness,

Crai