| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merging multiple workbooks with crosslinked formulas into single f | Excel Discussion (Misc queries) | |||
| Creating a single workbook from multiple workbooks | Excel Discussion (Misc queries) | |||
| Consolidate multiple workbooks into a single worksheet | Excel Worksheet Functions | |||
| Excel 2003 Referencing multiple workbooks via single variable | Excel Worksheet Functions | |||
| How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
![Ken[_19_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
