springfield_rifles;487838 Wrote:
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


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 with
the tab name as the file name.

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


--
cjonz
------------------------------------------------------------------------
cjonz's Profile: http://www.thecodecage.com/forumz/member.php?userid=995
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=129183