Adding Tabs (Worksheets)

Sure, but a couple of questions for you first:

1) With what frequency are you doing this?
2) Are your .txt files in the same location?
3) Couldn't you open each .txt file, copy the contents and paste to your target workbook?
4) Do you want to do this via VBA?

Smitty

1) This should be a 1 time thing...
2) Yes - they are all in one folder (differnt file names obviously)
3) Yes- but thats what I was trying to avoid... (Copyng and pasting ~20 files into one sheet, if i can just open them into there.. they formatting will all be the same, so I can then do a mass change on them)
4) I don't know VBA.. I'm an intermediate excel user.. i haven't gotten that far yet..

Thanks!!!!!!!!!!!!!!

A start:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213816&Product=xlw2K

HTH

Well,

As you're only doing it one time and don't know VBA, I'd probably just bite the bullet and go with quick and dirty keystrokes.

Set up your Master workbook with 20 blank worksheets., Then open each .txt file, CTRL+A-->CTRL+C-->ALT+TAB-->CTRL+V-->ALT+TAB-->ALT+FX & repeat.

It really shouldn't take more than a fwe minutes.

Iridium's post is worth a look and is a good introduction to VBA. (How he remembers where all this stuff is I'll never know!)

Smitty

pennysaver said:

Well,
Iridium's post is worth a look and is a good introduction to VBA. (How he remembers where all this stuff is I'll never know!)

Smitty

Click to expand...

For info I've accumulated a **** load of info over the years but the majority of the resources I use (which are linked in the sticky Mr Excel recommended links ) are:

MS Knowledge Base
John WalkenBach's Spreadsheet page
Chip Pearson
Jon Peltier's Excel Page
Tushar Mehta
Stephen Bullen


There's loads more out there so no disrespect to anyone who's not on this list!
HTH

This might work for you. It will put all the files you open on one sheet in the same book.It even has an input box asking you what file to open. You can probably play with it to make it work for your specific needs.

Sub Consolidate()
Dim NextFile As String
Dim Merged As Object
Dim Data As Object
Dim ws As Worksheet
Set Data = ActiveWorkbook
OpenNext:
Do
On Error GoTo ErrorHdlr
NewwbkName = Application.GetOpenFilename
Workbooks.Open (NewwbkName)
Set Merged = ActiveWorkbook
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
EndRow = Range("A65536").End(xlUp).Row
Range("A1:E" & EndRow).Select
Selection.Copy
Data.Activate
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Next ws
Application.DisplayAlerts = False
Merged.Close
Application.DisplayAlerts = True
Loop
Exit Sub

ErrorHdlr:
NextEntry = MsgBox("No File Selected!" & vbCr & _
vbCr & "Click Retry to open a file or Cancel.", _
vbRetryCancel, "NO FILE SELECTED")
If NextEntry = vbRetry Then
Resume OpenNext
End If
End Sub