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?


"StargateFanNotAtHome" wrote:

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, :oD