Dividing worksheet into multiple workbooks

[MadMak0203]

I have a worksheet in excel that has financial data for severa
departments and schools. The data is across 2 columns and 948 rows.
need to break the data down into multiple workbooks so I can use an
existing email macro to send them to managers.

There is a unique character that separates the data and I was able t
use a previously written macro to enter page breaks which is below, bu
I'm having trouble using the same unique character (or page break) t
put the data into separate workbooks. Any ideas?

Dim PBRange As Range, PB As Range
With ActiveSheet
Set PBRange = .Range("A1:A1000")
For Each PB In PBRange
If PB.Value = "***********" Then
.HPageBreaks.Add before:=PB
End If
Next
End Wit

 

[Bob Flanagan]

Take a look at the Data Extraction Assistant. It is designed to create
multiple workbooks from one worksheet.

http://www.add-ins.com/data_extraction_assistant.htm

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Don Guillett]

I have a worksheet in excel that has financial data for several
departments and schools. The data is across 2 columns and 948 rows. I
need to break the data down into multiple workbooks so I can use and
existing email macro to send them to managers.

There is a unique character that separates the data and I was able to
use a previously written macro to enter page breaks which is below, but
I'm having trouble using the same unique character (or page break) to
put the data into separate workbooks. Any ideas?

Dim PBRange As Range, PB As Range
With ActiveSheet
Set PBRange = .Range("A1:A1000")
For Each PB In PBRange
If PB.Value = "***********" Then
HPageBreaks.Add before:=PB
End If
Next
End With

Instead why not use a macro to copy the worksheet to a new book>email active sheet,ie:

for each ws in worksheets
ws.copy
activeworkbook.email
ws.close
next ws

 

[GS]

It happens that Don Guillett formulated :

I think you really meant...

for each ws in worksheets
ws.copy
activeworkbook.email
activeworkbook.close

next ws

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion