Multiple Workbooks to a Single Sheet
 

How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

I do need help, Ron, as a beginner with VBA. I copied the code for
"Copy a column or columns from each workbook" from
http://www.rondebruin.nl/copy3.htm into a VBA module for Excel, then
pressed f5. I saw the file names (which are in c:\data) roll by on
the left, but I couldn't find a result on an Excel page. Also, I'm
trying to copy columns A-M from each workbook and I'm not clear how to
change the code from your page (copied below). Sorry for beginner's
questions.

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

'With sourceRange
' Set destrange = basebook.Worksheets(1).Columns(Colnum). _
' Resize(, .Columns.Count)

Ken



"Ron de Bruin" wrote in message ...
Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

Hi Ken

Set sourceRange = mybook.Worksheets(1).Columns("A:A")

change to

Set sourceRange = mybook.Worksheets(1).Columns("A:M")

It will copy the the columns from the first Worksheet of each workbook in the
first worksheet in the workbook where you copy the macro.

You can also use a sheet name in the code if you want
In the example I use the sheet index (worksheets(1) is the first worksheet in the Tab order)


Don't forget there are only 256 columns

256/13 columns = ? files


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
I do need help, Ron, as a beginner with VBA. I copied the code for
"Copy a column or columns from each workbook" from
http://www.rondebruin.nl/copy3.htm into a VBA module for Excel, then
pressed f5. I saw the file names (which are in c:\data) roll by on
the left, but I couldn't find a result on an Excel page. Also, I'm
trying to copy columns A-M from each workbook and I'm not clear how to
change the code from your page (copied below). Sorry for beginner's
questions.

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

'With sourceRange
' Set destrange = basebook.Worksheets(1).Columns(Colnum). _
' Resize(, .Columns.Count)

Ken



"Ron de Bruin" wrote in message ...
Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

Ron -

I had tried changing A:A to A:M, but this time it worked (using 4 of
my 50 files). However, I want each original sheet to be placed below
the preceding sheet rather than to its right. I tried changing

Set destrange = basebook.Worksheets(1).Columns(Colnum)

to

Set destrange = basebook.Worksheets(1).rows(rownum)

but that didn't work (error message). How did I get vertical
compilation. Thanks.

Ken



****
"Ron de Bruin" wrote in message ...
Hi Ken

Set sourceRange = mybook.Worksheets(1).Columns("A:A")

change to

Set sourceRange = mybook.Worksheets(1).Columns("A:M")

It will copy the the columns from the first Worksheet of each workbook in the
first worksheet in the workbook where you copy the macro.

You can also use a sheet name in the code if you want
In the example I use the sheet index (worksheets(1) is the first worksheet in the Tab order)


Don't forget there are only 256 columns

256/13 columns = ? files


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
I do need help, Ron, as a beginner with VBA. I copied the code for
"Copy a column or columns from each workbook" from
http://www.rondebruin.nl/copy3.htm into a VBA module for Excel, then
pressed f5. I saw the file names (which are in c:\data) roll by on
the left, but I couldn't find a result on an Excel page. Also, I'm
trying to copy columns A-M from each workbook and I'm not clear how to
change the code from your page (copied below). Sorry for beginner's
questions.

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

'With sourceRange
' Set destrange = basebook.Worksheets(1).Columns(Colnum). _
' Resize(, .Columns.Count)

Ken



"Ron de Bruin" wrote in message ...
Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

Hi Ken

Use the range example then
http://www.rondebruin.nl/copy3.htm#Range

I am working on a update today of the page
Check it out this week

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
Ron -

I had tried changing A:A to A:M, but this time it worked (using 4 of
my 50 files). However, I want each original sheet to be placed below
the preceding sheet rather than to its right. I tried changing

Set destrange = basebook.Worksheets(1).Columns(Colnum)

to

Set destrange = basebook.Worksheets(1).rows(rownum)

but that didn't work (error message). How did I get vertical
compilation. Thanks.

Ken



****
"Ron de Bruin" wrote in message ...
Hi Ken

Set sourceRange = mybook.Worksheets(1).Columns("A:A")

change to

Set sourceRange = mybook.Worksheets(1).Columns("A:M")

It will copy the the columns from the first Worksheet of each workbook in the
first worksheet in the workbook where you copy the macro.

You can also use a sheet name in the code if you want
In the example I use the sheet index (worksheets(1) is the first worksheet in the Tab order)


Don't forget there are only 256 columns

256/13 columns = ? files


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
I do need help, Ron, as a beginner with VBA. I copied the code for
"Copy a column or columns from each workbook" from
http://www.rondebruin.nl/copy3.htm into a VBA module for Excel, then
pressed f5. I saw the file names (which are in c:\data) roll by on
the left, but I couldn't find a result on an Excel page. Also, I'm
trying to copy columns A-M from each workbook and I'm not clear how to
change the code from your page (copied below). Sorry for beginner's
questions.

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

'With sourceRange
' Set destrange = basebook.Worksheets(1).Columns(Colnum). _
' Resize(, .Columns.Count)

Ken



"Ron de Bruin" wrote in message ...
Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken

Multiple Workbooks to a Single Sheet
 

Hi Ken

I have done a update
http://www.rondebruin.nl/copy3.htm

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Ken

Use the range example then
http://www.rondebruin.nl/copy3.htm#Range

I am working on a update today of the page
Check it out this week

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
Ron -

I had tried changing A:A to A:M, but this time it worked (using 4 of
my 50 files). However, I want each original sheet to be placed below
the preceding sheet rather than to its right. I tried changing

Set destrange = basebook.Worksheets(1).Columns(Colnum)

to

Set destrange = basebook.Worksheets(1).rows(rownum)

but that didn't work (error message). How did I get vertical
compilation. Thanks.

Ken



****
"Ron de Bruin" wrote in message ...
Hi Ken

Set sourceRange = mybook.Worksheets(1).Columns("A:A")

change to

Set sourceRange = mybook.Worksheets(1).Columns("A:M")

It will copy the the columns from the first Worksheet of each workbook in the
first worksheet in the workbook where you copy the macro.

You can also use a sheet name in the code if you want
In the example I use the sheet index (worksheets(1) is the first worksheet in the Tab order)


Don't forget there are only 256 columns

256/13 columns = ? files


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
I do need help, Ron, as a beginner with VBA. I copied the code for
"Copy a column or columns from each workbook" from
http://www.rondebruin.nl/copy3.htm into a VBA module for Excel, then
pressed f5. I saw the file names (which are in c:\data) roll by on
the left, but I couldn't find a result on an Excel page. Also, I'm
trying to copy columns A-M from each workbook and I'm not clear how to
change the code from your page (copied below). Sorry for beginner's
questions.

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

'With sourceRange
' Set destrange = basebook.Worksheets(1).Columns(Colnum). _
' Resize(, .Columns.Count)

Ken



"Ron de Bruin" wrote in message ...
Hi Ken

Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken" wrote in message om...
How can I compile data from 50 different workbooks (single worksheet
each) on a single worksheet (vertically)? Each file is in the same
format and the files are on a cd rom. Thanks.

Ken