Hi,

I need a macro to copy columns from multiple workbooks into one, but into
separate worksheets.

I have budget files which have headers like product name, department, ID,
and quaterly buget (Q1'10, Q2'10, Q3'10........Q4'15), but the header order
is different in each file and they contain addtional information.
I just want to extract columns what I need.

Another problem is range of quaterly budget is different from each files.
Some files has Q1'09, but some don't. I need to copy all columns starting
"Q".

How can I do all this thing?

Thank you in advance.

Jen,

Sounds like there would be two parts to your program. The first would
identify the columns that need to be copied and the second part would
actually copy the columns.

If you are cycling through every worksheet in every open workbook, you can
use code similar to the following:

Public Sub test()

Dim bk As Workbook

For Each bk In Workbooks

For Each sht In bk.Sheets

Set rng = sht.Range("a1")

If Left(rng.Value, 1) = "Q" Then

'denote as column to be copied, or actually copy to
destination

End If

Next

Next

End Sub
--
Regards,
Eddie
http://www.HelpExcel.com


"Jen" wrote:

Hi,

I need a macro to copy columns from multiple workbooks into one, but into
separate worksheets.

I have budget files which have headers like product name, department, ID,
and quaterly buget (Q1'10, Q2'10, Q3'10........Q4'15), but the header order
is different in each file and they contain addtional information.
I just want to extract columns what I need.

Another problem is range of quaterly budget is different from each files.
Some files has Q1'09, but some don't. I need to copy all columns starting
"Q".

How can I do all this thing?

Thank you in advance.