combining multiple worksheets from many different files into single worksheet

[jduivest]

Hi there, I don't have much experience with macros, but I've recently started using them to process a large amount of data. I'm on (what I hope is) the final step, but I'm stuck, so I need a bit of help. I've done a search and found several similar questions, but unfortunately none exactly like this.

Essentially, I have several hundred spreadsheets in a folder. Each of these has two worksheets (lets call them Sheet X and Sheet Y). What I'd like to do is set up a macro that will automatically import this information into a master file. Each file is for a different day, and I'd like to combine everything so I can look at changes over time.

The details:

Each Sheet X has data entries on several different columns, from Row 1 to Row N. I'd like the master file will have a Sheet X, where all Sheet X (one from each spreadsheet file) are added sequentially. So Day 1 Sheet X is copied in from Rows 1 to N, Day 2 Sheet X is copied in from Rows N+1 to 2N, etc.

The same process happens for Sheet Y.

All files are formatted identically, and are in the same folder. I'm using Excel 2007, if that matters.

Note, Sheet X and Sheet Y are not linked to each other, so it would be fine for them to be in two different files. The important thing is that all Sheet X are combined into a single master Sheet X, and all Sheet Y are combined into a single master Sheet Y.

Thanks so much!!!

 

[MrsGKL]

Here’s how I would accomplish this<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I didn’t test and you may have to tweak it but it should get you going<o></o>
<o> </o>
-use a DOS command to get a list of the workbooks in your folder<o></o>
Example: DOS Command dir /a "C:\Program Files\xorb\" > C:\dirlist.txt<o></o>
(if you need help on above, google it)<o></o>
<o> </o>
Create a Masterlist Workbook<o></o>
Create 3 Sheets<o></o>
*Sheet X<o></o>
*Sheet Y<o></o>
*Workbooks (let’s say there are 100 workbooks in your folder)<o></o>
-in above Workbooks worksheet, paste a list of the complete file paths of your workbooks in column A (e.g. c:\temp\mydoc.xls)<o></o>
<o> </o>
Dim MasterListWorkbook as Workbook<o></o>
Set MasterListWorkbook as ActiveWorkbook<o></o>
<o> </o>
Dim WorkbookFilePath as String<o></o>
<o> </o>
Dim i as Integer ‘for workbook<o></o>
Dim j as Long ‘row on masterlist Sheet X<o></o>
Dim k as Long ‘row on masterlist Sheet Y<o></o>
Dim lastrow as Long ‘last row on the workbook we are obtaining data from<o></o>
<o> </o>
Dim OpenWorkbook as Workbook<o></o>
<o> </o>
j = 1<o></o>
k = 1<o></o>
<o> </o>
For i = 1 to 100<o></o>
<o> </o>
MasterListWorkbook.Activate<o></o>
Worksheets(“Workbooks”).Activate<o></o>
WorkbookFilePath = Cells(i,1)<o></o>
<o></o>
Workbooks.Open (WorkbookFilePath)<o></o>
Set OpenWorkbook = ActiveWorkbook<o></o>
<o></o>
Worksheets(“Sheet X”).Activate<o></o>
LastRow = Cells.SpecialCells(xlcelltypelastcell).Row<o></o>
Rows(“1:” & LastRow).Copy<o></o>
MasterListWorkbook.Activate<o></o>
Worksheets(“Sheet X”).Activate<o></o>
Range(“A” & j).Paste<o></o>
j = j + LastRow<o></o>
<o> </o>
OpenWorkbook.Activate<o></o>
Worksheets(“Sheet Y”).Activate<o></o>
LastRow = Cells.SpecialCells(xlcelltypelastcell).Row<o></o>
Rows(“1:” & LastRow).Copy<o></o>
MasterListWorkbook.Activate<o></o>
Worksheets(“Sheet Y”).Activate<o></o>
Range(“A” & k).Paste<o></o>
k = k + LastRow<o></o>
<o> </o>
OpenWorkbook.Close SaveChanges:=False<o></o>
<o> </o>
Next i<o></o>

 

[jduivest]

Hi, thanks for the quick reply.

I've pasted it, and given it a try.

MasterListWorkbook.Activate didn't seem to work, so I replaced it with Windows("MasterListWorkbook.xlsx").Activate

AFAIK, this should also be ok, as long as I have the file open at the time?

However, I'm stuck at the following:
Range("A" & j).Paste

I'm being told this property/method isn't supported. Any suggestions?

Thanks

 

[MrsGKL]

Maybe Rows(j).Paste

 

[jduivest]

Yahoo, got it to work. Thanks!

Just in case anyone else has the same issue, here's a few changes I made from the original code, to fix the bugs that came up.


Add
Application.CutCopyMode = False
before k = k + lastrow.

This prevents a dialog box from popping up every time the macro tries to close the files you're copying from.

-----

I couldn't get the Rows command to work, so I used the following:
Range("A1").Offset(j, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

-----
Workbooks.Open (WorkbookFilePath)

was replaced by

Windows("MasterListWorkbook.xlsx").Activate

Other than that, it worked great! Thanks again.