Merge 7 files to 1 workbook/7Sheets and then 1 sheet of the newworkbook

[Snailspace]

I have to combine 7 worksheets from 7 files on my desktop into 1
workbook - then create 1 worksheet of all the data in this same
workbook except the column header row 1. I tried doing a recording
to
make my own macro but I get an error when trying to copy the sheet to
the workbook and sheet.

what do I use to copy the data to a new workbook/worksheet ??


Copy Before:=Workbooks("Book2").Sheets(1)


This doesn't work....so I want to try and figure this out.

Thanks!!

 

[Otto Moehrbach]

From what you say, you have 8 workbooks. One workbook, call it Total.xls,
is blank. The headers of the other 7 workbooks are the same. You want
these headers placed in the Total workbook, row 1, then you want all the
data, from row 2 down, from each of the 7 workbooks, placed into the Total
workbook, one after the other, going down the sheet. Is this right? HTH
Otto

 

[Snailspace]

I have 7 workbooks and I want to create the 8th from the data files.
My 8th workbook would have all the data from 08-09 from each of the
sheets from the 7 - PLUS an 8th worksheet in this 8th workbook that
would combine the data so I can filter and sort it as I need to. Does
this help?

Thanks!!

 

[Otto Moehrbach]

Excel needs to find those 7 workbooks. To facilitate this, I will assume
that you have those 7 files, AND NO OTHER FILES, in one folder. I will
further assume that the pertinent sheet in each of the 7 workbooks has the
same name in all the 7 workbooks. I will further assume that the 8th
workbook exists and the code will be placed in that workbook. That 8th
workbook will initially have only one sheet and that sheet will be named
"Combined". I will further assume that the first 7 sheets in the 8th
workbook will be named the name of the 7 workbooks (the code will add those
sheets and name them). How does that sound to you? Otto
I have 7 workbooks and I want to create the 8th from the data files.
My 8th workbook would have all the data from 08-09 from each of the
sheets from the 7 - PLUS an 8th worksheet in this 8th workbook that
would combine the data so I can filter and sort it as I need to. Does
this help?

Thanks!!