Thanks Bob!

K

Katie

Thanks Bob I appreciate it. My girlfriends and I are
just getting lazy I guess. The guys in accounting use
some funky software and our excel values don't translate
I guess. My friend Lauren was like "isn't there a button
you can press to take all the sheets and make them all
individual files, instead of doing one at a time?" Like
I said, I guess were just getting lazy. It's just that
some of our excel files have like a million sheets (more
like 30 but you get the point.) Thanks a bunch!
 
B

Bob Phillips

Katie (and Lauren)

If you must be lazy, here is some code

Sub Macro1()

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
sh.Move
With ActiveWorkbook
.SaveAs Filename:=sh.Name & ".txt", _
FileFormat:=xlText, _
CreateBackup:=False
.Close
Next sh
Application.DisplayAlerts = True

End Sub

Something to be wary of though. I name the txet file by the sheet name. That
may not be good enough as the sheet name may be replicated in other books.
It may need a better strategy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
E

Ed

Maybe add the date to the filename?
.SaveAs Filename:=sh.Name & Format(Now, "mm-dd-yy") & ".txt",
 
K

Katie

Who rocks..... Bob Rocks!!!

-----Original Message-----
Katie (and Lauren)

If you must be lazy, here is some code

Sub Macro1()

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
sh.Move
With ActiveWorkbook
.SaveAs Filename:=sh.Name & ".txt", _
FileFormat:=xlText, _
CreateBackup:=False
.Close
Next sh
Application.DisplayAlerts = True

End Sub

Something to be wary of though. I name the txet file by the sheet name. That
may not be good enough as the sheet name may be replicated in other books.
It may need a better strategy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
D

David McRitchie

When including a date in filename or in a worksheet
for that matter, you want to use 4 digit year first, then month
and day of month both 2 digits so that you can see them arranged
properly in alphabetical order. An example for saving a
backup file in http://www.mvps.org/dmcritchie/excel/backup.htm
though that is for the entire file not like your are doing per sheet.
 
Top