Merge worksheets from separate files into one workbook.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several single-worksheet Excel files/workbooks that I would like to
merge into a single file/workbook. Any idea how I can turn these into one?
Thx!
 
dagriffin said:
I have several single-worksheet Excel files/workbooks that I would like to
merge into a single file/workbook. Any idea how I can turn these into one?
Thx!
Click to expand...

To do this, save one of the documents as your entire book, then, right
click on the bottom tab of the other documents you want to move, and hit move
or copy. on this selection at the top is an arrow, says move to book, arrow
down until you see the name of your new file. Make sure you click the box at
the bottom of the window that says make a copy so you won't loose your
original (unless you don't need it anymore) Do this with every file.
 
Open the first single-sheet workbook and File / Save As, and give it the new
file name. Keep that file open.
In turn, open each remaining single-sheet workbook and right-click on its
worksheet tab, select Move or Copy and use the drop-down to choose the name
of the workbook you created in the first step above. Don't select the 'copy'
checkbox, and click OK.
When you've done this w/each single-sheet workbook, you'll have them all
compiled in one.
IF each original file uses the same worksheet name, you'll probably want to
rename the worksheets before moving them so you can tell which sheet is which
(right-click on the tab and select Rename, then type in the new name).
 
open all excel files
use a macro that will copy the data and paste them to the end of the next workbook by using ctrl+tab
me also had the same "problem" and the method worked
 
See other reply below...

Lilsis7 said:
To do this, save one of the documents as your entire book, then, right
click on the bottom tab of the other documents you want to move, and hit move
or copy. on this selection at the top is an arrow, says move to book, arrow
down until you see the name of your new file. Make sure you click the box at
the bottom of the window that says make a copy so you won't loose your
original (unless you don't need it anymore) Do this with every file.
Click to expand...
 
Guess I should have been clearer. I have 71 of these files to compress into
one. I can do it manually as suggested, but was looking for something more
automated as this will be a repetitive weekly task going forward. Any
functions within Excel or macro tips that would allow this to be done with
the "press of a button" for all 71 files?
 
I have done somthing similiar .. mining data from several seperated exel
files and merging them into one. I found the kernal of this in the help
files. I cut and paste this from my working vba ..hope it give you some idea
of one path to take.

Sub test()
Application.Calculation = xlManual 'stop auto calculation
ifile = 129 'first file
number-1
For i = 1 To 5 'loop on
files=130 to 134
namefile = "Flow" + CStr(ifile+ i) + ".xls"
Name = "C:\" + namefile 'set file name"
Set MyXL = GetObject(Name) 'Set the object
variable to reference the file you want to see.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True 'note window(1) is
the parent worksheet , window(2) is worksheet being read on

For j = 21 To 50 'grab data from (row 2
,col 21 thru 48)
a = Workbooks(namefile).Worksheets("Data").Cells(2, j) 'grab info
from worksheet and add it to parent worksheet
ThisWorkbook.Worksheets(1).Cells(i, j - 20) = a 'put data in
(row i,col 1 to 28)
Next j
'close the sheet
MyXL.Save 'save loop worksheet (using saveas and other it
can be closed without saving)
MyXL.Close 'close loop worksheet
Set MyXL = Nothing ' Release reference to the
Next i
Application.Calculation = xlAutomatic 'turn on recalculation
End Sub
 
Back
Top