printing - creating new files on each page break
 

I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan

Try this tester with the sheet active on a copy of your workbook
it will look at the HPageBreaks

Sub test()
Dim HPB As HPageBreak
Dim rw As Long
Dim shnum As Long
Dim Asheet As Worksheet
Dim Nsheet As Worksheet

Set Asheet = ActiveSheet
rw = 1
shnum = 1

For Each HPB In Asheet.HPageBreaks
Set Nsheet = Worksheets.Add(after:=ActiveWorkbook.Sheets(Active Workbook.Sheets.Count))
Nsheet.Name = "Page " & shnum
With Asheet
.Range(.Cells(rw, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
rw = HPB.Location.Row
shnum = shnum + 1
Next HPB
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message ...
I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan

Ron,

thanks very much. I've got one more situation that's similar. I have a
report that's very long and would like to create seperate files when the
contents of column A (Location for me) changes. It's about 8,000 rows and
creating seperate files for each week isn't possible. It would be very
helpful to have something that would do this.

regards,

"Ron de Bruin" wrote:

Try this tester with the sheet active on a copy of your workbook
it will look at the HPageBreaks

Sub test()
Dim HPB As HPageBreak
Dim rw As Long
Dim shnum As Long
Dim Asheet As Worksheet
Dim Nsheet As Worksheet

Set Asheet = ActiveSheet
rw = 1
shnum = 1

For Each HPB In Asheet.HPageBreaks
Set Nsheet = Worksheets.Add(after:=ActiveWorkbook.Sheets(Active Workbook.Sheets.Count))
Nsheet.Name = "Page " & shnum
With Asheet
.Range(.Cells(rw, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
rw = HPB.Location.Row
shnum = shnum + 1
Next HPB
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message ...
I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan

Hi Tere

You can use this example then
http://www.rondebruin.nl/copy5.htm

Look at this example
http://www.rondebruin.nl/copy5.htm#workbook



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message ...
Ron,

thanks very much. I've got one more situation that's similar. I have a
report that's very long and would like to create seperate files when the
contents of column A (Location for me) changes. It's about 8,000 rows and
creating seperate files for each week isn't possible. It would be very
helpful to have something that would do this.

regards,

"Ron de Bruin" wrote:

Try this tester with the sheet active on a copy of your workbook
it will look at the HPageBreaks

Sub test()
Dim HPB As HPageBreak
Dim rw As Long
Dim shnum As Long
Dim Asheet As Worksheet
Dim Nsheet As Worksheet

Set Asheet = ActiveSheet
rw = 1
shnum = 1

For Each HPB In Asheet.HPageBreaks
Set Nsheet = Worksheets.Add(after:=ActiveWorkbook.Sheets(Active Workbook.Sheets.Count))
Nsheet.Name = "Page " & shnum
With Asheet
.Range(.Cells(rw, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
rw = HPB.Location.Row
shnum = shnum + 1
Next HPB
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message
...
I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan

I add a basic example on my site for the pagebreak copy
http://www.rondebruin.nl/hpagebreaks.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Tere

You can use this example then
http://www.rondebruin.nl/copy5.htm

Look at this example
http://www.rondebruin.nl/copy5.htm#workbook



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message ...
Ron,

thanks very much. I've got one more situation that's similar. I have a
report that's very long and would like to create seperate files when the
contents of column A (Location for me) changes. It's about 8,000 rows and
creating seperate files for each week isn't possible. It would be very
helpful to have something that would do this.

regards,

"Ron de Bruin" wrote:

Try this tester with the sheet active on a copy of your workbook
it will look at the HPageBreaks

Sub test()
Dim HPB As HPageBreak
Dim rw As Long
Dim shnum As Long
Dim Asheet As Worksheet
Dim Nsheet As Worksheet

Set Asheet = ActiveSheet
rw = 1
shnum = 1

For Each HPB In Asheet.HPageBreaks
Set Nsheet = Worksheets.Add(after:=ActiveWorkbook.Sheets(Active Workbook.Sheets.Count))
Nsheet.Name = "Page " & shnum
With Asheet
.Range(.Cells(rw, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
rw = HPB.Location.Row
shnum = shnum + 1
Next HPB
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tere Gardner" wrote in message
...
I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan