printing - creating new files on each page break

[Guest]

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,

 

[Ron de Bruin]

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(ActiveWorkbook.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

 

[Guest]

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]

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

 

[Ron de Bruin]

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