Split file, saving each tab/worksheet to a new workbook, w/tab asworkbook name?

[StargateFanNotAtHome]

I had a light-bulb moment this morning to resolve a months-long
problem ... can Excel via a script split up a file by saving each
worksheet to a separate workbook and taking each new workbook's name
from the sheet's original tab name?

I've found a couple of somewhat similar requets here:
http://www.ozgrid.com/forum/showthread.php?t=14120, and here:
http://groups.google.ca/group/micro...=en&lnk=gst&q=split+workbook#8017ee2c1e6a95e2,
where the first one gives me an easier-looking script that seems
potentially more modifiable -- at least, to my less knowledgeable eyes
-- to what needs to be done <g>:

--------------------------------------------
Sub Movesheets()
' Macro by WillR
' www.ozgrid.com

Dim oldBook As Workbook
Dim newBook As Workbook


Set oldBook = ActiveWorkbook
Set newBook = Workbooks.Add
newBook.Activate
oldBook. Sheets("Sheet2").Copy after:=newBook.Sheets("Sheet2")
ActiveSheet. Name = "NewSheet"
ActiveWorkbook. SaveAs FileName:=Format( Date, "MMMMYY") &
"Report.xls"
--------------------------------------------

Of course, the difference is that this one saves only sheet 2 to a new
workbook and gives it a specific name where I need all the worksheets
to be saved to new, individual workbooks and to have them named by
their sheet name.

Thanks! D

 

[Gord Dibben]

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs Filename:=ActiveWorkbook.Path _
& "\" & w.Name & ".xlsx" 'or .xls
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[StargateFanNotAtHome]

Pretty freakin' amazing!!! XL is such a miracle! Yes, this seems to
have worked really, really well.

I was wondering about one thing, though ... when I run the script, I
got a bunch of workbooks but it's weird where they got saved. I saw
the script working as "boxes" would appear on the right of the taskbar
"box" for the file itself. The names would change on-the-fly from
Book1, etc., to the new tab sheet name and it was pretty kewl to watch
this process in action. But after, when I activated the window of the
open folder where the file is located, no new workbooks. I hunted
around for them and found all of them in a completely different
folder. Is there a way to change ActiveWorkbook.Path _ to whatever it
is that Excel would understand as being the same folder the file is
in? That would then make this as close to perfect as is possible. <g>

Thx. D

 

[StargateFanNotAtHome]

Okay, this usually doesn't happen since I don't know the terminology
well in vb, but today I got lucky. I googled for ActiveWorkbook.Path,
same as I might always do, but this time I did stumble upon an
alternative, ThisWorkbook.Path and this _did_ save the new workbooks
to the same folder as the original. How come the ActiveWorkbook.Path
saves to our personal folder here at work I'll never know.
ActiveWorkbook.Path logically sounds like it would deal with the same
folder but there must be some quirk here in the office that it sends
it off to my "personal" root network work folder. I also added a sort
of prefix to the new workbook name so that in case the person they
staffed this position with happens to do this in a folder populated
with other files, the workbooks will all stay grouped together because
of the "zFileSplit- " at the beginning of each file. So the slightly
modified vb script looks like this now:

---------------------------------------------------
Sub WORKBOOK_Split_and_new_files_named_by_tab()

Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path _
& "\zSplitFile- " & w.Name & ".xls" 'or .xlsx; if you're
using XL2007 fully and not in compatibility mode, use .xlsx instead
of .xls
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
---------------------------------------------------

Thanks so much! This will make it a heck of a lot easier to manage
their huge Excel file.

Cheers, D

 

[springfield_rifles]

I tried this program and it giving a runtime error '1004'; the sheet you are
copying contains cells that have more then 255 characters. When you copy the
sheet only the first 255 characters in each cell will be copied. To copy all
the characters, copy the cells to a new sheet instead of copying the entire
sheet.

When I try to debug it highlights this line: w.Copy

Is there a value I can put in that will fix this problem?

 

[cjonz]

I tried this program and it giving a runtime error '1004'; the sheet yo
are
copying contains cells that have more then 255 characters. When yo
copy the
sheet only the first 255 characters in each cell will be copied. T
copy all
the characters, copy the cells to a new sheet instead of copying th
entire
sheet.

When I try to debug it highlights this line: w.Copy

Is there a value I can put in that will fix this problem?

I would like to take this a step further by doing 2 more things:

(1.) I would like to save each tab/worksheet as a seperate .PDF wit
the tab name as the file name.

(2.) Would it be possible to first (1.) and save each seperate .PDF i
seprate folders in the same directory as the ActiveWorkbook where th
Folder name is the same as the tab name