Dividing worksheet into multiple workbooks
 

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 befo=PB
End If
Next
End With

Dividing worksheet into multiple workbooks
 

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



On May 31, 4:04*pm, MadMak0203
wrote:
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 befo=PB
End If
Next
End With

--
MadMak0203

Dividing worksheet into multiple workbooks
 

On Thursday, May 31, 2012 3:04:28 PM UTC-5, MadMak0203 wrote:
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 befo=PB
End If
Next
End With




--
MadMak0203

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

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

Dividing worksheet into multiple workbooks
 

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