MS Excel: Quick way to put multiple sheets into a single workbook?

Question

I often want to work with many independent text files as excel worksheets, and to organize them into a single workbook is cumbersome (right click worksheet tab, "move or copy", etc., each one).  

Is there a shortcut or setting that will allow me to, say, open all the files at once combined into a single workbook?  Or to collapse all open workbooks into a single workbook?

Cerdle · Accepted Answer

There is a way to do what you ask,but it requires using some macros.  I am not sure how comfortable you are using them, but I wrote you some quick code that will do what you ask.  It is not my best work, but I did it relatively quickly so all you coders out there no laughing ;)

To make this work, follow these steps:

1) Open a blank Excel Workbook.
2) Press Alt-F11 on your keyboard to enter the Visual Basic for Applications IDE.
3) From the file menu, click on Insert Module
4) Paste the code that I have below into the module.
5) On the 12th line of code is a line that reads:
    MyPath = "c:&#92;text&#92;"    ' Set the path.

Replace the path on the quotes with the path that you keep your text files.  Be sure to end it with the slash " &#92; ".


5) Save your Workbook and close the IDE (Alt-Q)
6) From the Excel Worksheet, Alt-F8 to open the Run Macros window.
7)  Look in the list for one called 'ReadDir;
8) Select this macro
 
If all goes well it should open each text file in the directory and move the file into the activeworkbook.  It will rename each tab the name of the text file.  Hope this meets your needs.

================================
Sub ReadDir()
Dim MyPath As String
Dim MyFile As String
Dim xlrow As Long
Dim txtNumber As Integer
Dim MyWorkbook As String
Dim MyOpenWorkbook As String
Dim fixFileName As String

MyWorkbook = ActiveWorkbook.Name

MyPath = "c:&#92;text&#92;"    ' Set the path.

MyFile = Dir(MyPath & "*.txt", vbDirectory)

Do While MyFile <> ""
txtFile = MyPath & MyFile
    Workbooks.OpenText Filename:=txtFile, StartRow:= _
        1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False
    MyOpenWorkbook = ActiveWorkbook.Name
    
    If Right(MyFile, 4) = ".txt" Then
        fixFileName = Left(MyFile, Len(MyFile) - 4)
    End If
    Sheets(fixFileName).Select
    Sheets(fixFileName).Copy After:=Workbooks(MyWorkbook).Sheets(1)
    Workbooks(MyOpenWorkbook).Close False

    
    
    MyFile = Dir
Loop


End Sub

Anonymous · Answer

Highlight the whole sheet by left clicking on the area (box) in the very top left hand corner of the spreadsheet. (above the "A" and the "1," Copy and paste sheet into new workbook.

saqeelahmed_2000 · Answer

you can import or export the sheets or workbook or some selected stuff.....IMPORT / EXPORT

Anonymous · Answer

coy and paste

Trending News

MS Excel: Quick way to put multiple sheets into a single workbook?

4 Answers