software

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!



quot;dagriffinquot; wrote:

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

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

quot;dagriffinquot; wrote:

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

See other reply below...

quot;Lilsis7quot; wrote:

gt;
gt;
gt; quot;dagriffinquot; wrote:
gt;
gt; gt; I have several single-worksheet Excel files/workbooks that I would like to
gt; gt; merge into a single file/workbook. Any idea how I can turn these into one?
gt; gt; Thx!
gt;
gt; To do this, save one of the documents as your entire book, then, right
gt; click on the bottom tab of the other documents you want to move, and hit move
gt; or copy. on this selection at the top is an arrow, says move to book, arrow
gt; down until you see the name of your new file. Make sure you click the box at
gt; the bottom of the window that says make a copy so you won't loose your
gt; original (unless you don't need it anymore) Do this with every file.

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 quot;press of a buttonquot; for all 71 files?

quot;bpeltzerquot; wrote:

gt; Open the first single-sheet workbook and File / Save As, and give it the new
gt; file name. Keep that file open.
gt; In turn, open each remaining single-sheet workbook and right-click on its
gt; worksheet tab, select Move or Copy and use the drop-down to choose the name
gt; of the workbook you created in the first step above. Don't select the 'copy'
gt; checkbox, and click OK.
gt; When you've done this w/each single-sheet workbook, you'll have them all
gt; compiled in one.
gt; IF each original file uses the same worksheet name, you'll probably want to
gt; rename the worksheets before moving them so you can tell which sheet is which
gt; (right-click on the tab and select Rename, then type in the new name).
gt;
gt; quot;dagriffinquot; wrote:
gt;
gt; gt; I have several single-worksheet Excel files/workbooks that I would like to
gt; gt; merge into a single file/workbook. Any idea how I can turn these into one?
gt; gt; Thx!

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 = quot;Flowquot; CStr(ifile i) quot;.xlsquot;
Name = quot;C:\quot; namefile 'set file namequot;
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(quot;Dataquot;).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

quot;dagriffinquot; wrote:

gt; Guess I should have been clearer. I have 71 of these files to compress into
gt; one. I can do it manually as suggested, but was looking for something more
gt; automated as this will be a repetitive weekly task going forward. Any
gt; functions within Excel or macro tips that would allow this to be done with
gt; the quot;press of a buttonquot; for all 71 files?
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; Open the first single-sheet workbook and File / Save As, and give it the new
gt; gt; file name. Keep that file open.
gt; gt; In turn, open each remaining single-sheet workbook and right-click on its
gt; gt; worksheet tab, select Move or Copy and use the drop-down to choose the name
gt; gt; of the workbook you created in the first step above. Don't select the 'copy'
gt; gt; checkbox, and click OK.
gt; gt; When you've done this w/each single-sheet workbook, you'll have them all
gt; gt; compiled in one.
gt; gt; IF each original file uses the same worksheet name, you'll probably want to
gt; gt; rename the worksheets before moving them so you can tell which sheet is which
gt; gt; (right-click on the tab and select Rename, then type in the new name).
gt; gt;
gt; gt; quot;dagriffinquot; wrote:
gt; gt;
gt; gt; gt; I have several single-worksheet Excel files/workbooks that I would like to
gt; gt; gt; merge into a single file/workbook. Any idea how I can turn these into one?
gt; gt; gt; Thx!

Looks promising! Thanks for the suggestion, I will be starting from here!!

quot;Mark HOlcombquot; wrote:

gt; I have done somthing similiar .. mining data from several seperated exel
gt; files and merging them into one. I found the kernal of this in the help
gt; files. I cut and paste this from my working vba ..hope it give you some idea
gt; of one path to take.
gt;
gt; Sub test()
gt; Application.Calculation = xlManual 'stop auto calculation
gt; ifile = 129 'first file
gt; number-1
gt; For i = 1 To 5 'loop on
gt; files=130 to 134
gt; namefile = quot;Flowquot; CStr(ifile i) quot;.xlsquot;
gt; Name = quot;C:\quot; namefile 'set file namequot;
gt; Set MyXL = GetObject(Name) 'Set the object
gt; variable to reference the file you want to see.
gt; MyXL.Application.Visible = True
gt; MyXL.Parent.Windows(1).Visible = True 'note window(1) is
gt; the parent worksheet , window(2) is worksheet being read on
gt;
gt; For j = 21 To 50 'grab data from (row 2
gt; ,col 21 thru 48)
gt; a = Workbooks(namefile).Worksheets(quot;Dataquot;).Cells(2, j) 'grab info
gt; from worksheet and add it to parent worksheet
gt; ThisWorkbook.Worksheets(1).Cells(i, j - 20) = a 'put data in
gt; (row i,col 1 to 28)
gt; Next j
gt; 'close the sheet
gt; MyXL.Save 'save loop worksheet (using saveas and other it
gt; can be closed without saving)
gt; MyXL.Close 'close loop worksheet
gt; Set MyXL = Nothing ' Release reference to the
gt; Next i
gt; Application.Calculation = xlAutomatic 'turn on recalculation
gt; End Sub
gt;
gt;
gt;
gt;
gt; quot;dagriffinquot; wrote:
gt;
gt; gt; Guess I should have been clearer. I have 71 of these files to compress into
gt; gt; one. I can do it manually as suggested, but was looking for something more
gt; gt; automated as this will be a repetitive weekly task going forward. Any
gt; gt; functions within Excel or macro tips that would allow this to be done with
gt; gt; the quot;press of a buttonquot; for all 71 files?
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; Open the first single-sheet workbook and File / Save As, and give it the new
gt; gt; gt; file name. Keep that file open.
gt; gt; gt; In turn, open each remaining single-sheet workbook and right-click on its
gt; gt; gt; worksheet tab, select Move or Copy and use the drop-down to choose the name
gt; gt; gt; of the workbook you created in the first step above. Don't select the 'copy'
gt; gt; gt; checkbox, and click OK.
gt; gt; gt; When you've done this w/each single-sheet workbook, you'll have them all
gt; gt; gt; compiled in one.
gt; gt; gt; IF each original file uses the same worksheet name, you'll probably want to
gt; gt; gt; rename the worksheets before moving them so you can tell which sheet is which
gt; gt; gt; (right-click on the tab and select Rename, then type in the new name).
gt; gt; gt;
gt; gt; gt; quot;dagriffinquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have several single-worksheet Excel files/workbooks that I would like to
gt; gt; gt; gt; merge into a single file/workbook. Any idea how I can turn these into one?
gt; gt; gt; gt; Thx!