Adding Tabs (Worksheets) :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Adding Tabs (Worksheets)
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

HLS811
Board Regular


Joined: 28 Oct 2002
Posts: 19


Status: Offline

 Reply with quote  

Adding Tabs (Worksheets)

I have ~ 20 seperate .txt files i want to bring into excel as a single workbook - If I open them, its creating a seperate workbook for each - Is there a way to do a mass add of seperate files into 1 workbook and simply have them on seperate tabs (worksheets?)

Thanks in advance~

(I'm working in Excel 2000)

Post Fri Sep 19, 2003 4:02 pm 
 View user's profile Send private message

pennysaver
Board Master


Joined: 15 May 2003
Posts: 2189
Location: San Diego, California
Flag: Usa

Status: Offline

 Reply with quote  

Re: 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

Post Fri Sep 19, 2003 4:18 pm 
 View user's profile Send private message Visit poster's website MSN Messenger

HLS811
Board Regular


Joined: 28 Oct 2002
Posts: 19


Status: Offline

 Reply with quote  

Re: Adding Tabs (Worksheets)

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!!!!!!!!!!!!!!

Post Fri Sep 19, 2003 4:31 pm 
 View user's profile Send private message

Iridium
Board Master
Board Master


Joined: 16 Jul 2002
Posts: 2581
Location: Walsall, England
Flag: England

Status: Offline

 Reply with quote  

Re: Adding Tabs (Worksheets)

A start:

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

HTH
_________________

Post Fri Sep 19, 2003 4:54 pm 
 View user's profile Send private message

pennysaver
Board Master


Joined: 15 May 2003
Posts: 2189
Location: San Diego, California
Flag: Usa

Status: Offline

 Reply with quote  

Re: Adding Tabs (Worksheets)

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

Post Fri Sep 19, 2003 4:59 pm 
 View user's profile Send private message Visit poster's website MSN Messenger

Iridium
Board Master
Board Master


Joined: 16 Jul 2002
Posts: 2581
Location: Walsall, England
Flag: England

Status: Offline

 Reply with quote  

Re: Adding Tabs (Worksheets)

quote:
Originally posted by pennysaver:
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


For info I've accumulated a shit 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! icon_wink.gif
HTH
_________________

Post Fri Sep 19, 2003 5:25 pm 
 View user's profile Send private message

RolanDoobies
Board Regular


Joined: 29 Aug 2002
Posts: 84

Flag: Blank

Status: Offline

 Reply with quote  

Re: Adding Tabs (Worksheets)

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

Post Fri Sep 19, 2003 6:58 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.