Multiple Workbooks to a Single Sheet

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

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



****
 
Back
Top