open several files in one worksheet

L

littlebit

When I open a file into Excel which I say only one sheet it overwrites the
first file I opened. I do not want more than 1 sheet but several files
opened to it. I have Excel 97. Any answers please.
 
D

Dave Peterson

Maybe they're just hidden.

You could use alt-tab to toggle between open workbooks or select them from the
list under Window (on the menubar).
 
T

tghcogo

As Dave said they are hidden, ie overlaying each other.

Alt-Tab works fine, however you may prefer Ctrl-Tab, which toggles th
workbooks in view.

If you wish to see more than one workbook at the same time you can us
the restore button, next to the X button in the WINDOW, (NOT the ful
screen X button), and resize the individual windows by dragging th
bottom right hand corner.

hope this helps

TGHCOG
 
L

littlebit

Thank you Dave and tghcogo,

I really want these files on the same sheet. I have several files about
3,000 rows each and want them to run consequtively on the same sheet. One
worksheet in a single workbook. Any solutions?

littlebit
 
D

Dave Peterson

I think I misunderstood your question. You weren't asking why the
activeworkbook hides the inactive workbooks. You want to open some workbooks,
copy some cells/rows into a giant consolidated worksheet.

This kind of code depends on a lot of things--where the workbooks are located
(drive/folder), what worksheets (the first/only in the workbook or all or just
some).

I assumed that your files are all in the same folder. You'll get a File|open
dialog. Click on the first workbook and ctrl-click on subsequent workbooks.

This routine copies the data from first worksheet in each of the selected
workbooks into a new worksheet in a new workbook.

Sub testme()

Dim newWks As Worksheet
Dim myFileNames As Variant
Dim nextWkbk As Workbook
Dim wks As Worksheet
Dim fCtr As Long
Dim DestCell As Range
Dim RngToCopy As Range

myFileNames = Application.GetOpenFilename _
(FileFilter:="Excel files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
Set newWks = Workbooks.Add(1).Worksheets(1)
Set DestCell = newWks.Range("a1")
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set nextWkbk = Nothing
On Error Resume Next
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
On Error GoTo 0
If nextWkbk Is Nothing Then
MsgBox "Error with: " & myFileNames(fCtr)
Else
With nextWkbk.Worksheets(1)
Set RngToCopy = .Range("a1", _
.Cells.SpecialCells(xlCellTypeLastCell))
End With

RngToCopy.Copy _
Destination:=DestCell

Set DestCell = newWks.Cells.SpecialCells(xlCellTypeLastCell) _
.EntireRow.Cells(1).Offset(1, 0)

nextWkbk.Close savechanges:=False
End If
Next fCtr
Else
MsgBox "try again later!"
End If

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Thank you Dave and tghcogo,

I really want these files on the same sheet. I have several files about
3,000 rows each and want them to run consequtively on the same sheet. One
worksheet in a single workbook. Any solutions?

littlebit
Click to expand...
 
Top