ExcelBanter - How do I save one page out of a 80 page report in excel?

Hi bellamere

Try this tester that will copy page 4 to a new sheet
No error check to see if page 4 exist in this example

You can send this sheet with code or with my Add-in
http://www.rondebruin.nl/sendmail.htm

Sub Test_HPageBreak()
Dim HPB As HPageBreak
Dim RW As Long
Dim PageNum As Long
Dim WB As Workbook
Dim Asheet As Worksheet
Dim Nsheet As Worksheet
Dim Acell As Range

Set Asheet = ActiveSheet
If Asheet.HPageBreaks.Count = 0 Then
MsgBox "There are no HPageBreaks"
Exit Sub
End If
Application.ScreenUpdating = False
Set WB = ActiveWorkbook
'Because of this bug we select a cell below your data
'http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
Set Acell = ActiveCell
Application.Goto Range("A" & Rows.Count), True
RW = 1
PageNum = 1
For Each HPB In Asheet.HPageBreaks
If PageNum = 4 Then
Set Nsheet = Worksheets.Add(after:=WB.Sheets(WB.Sheets.Count))
On Error Resume Next
Nsheet.Name = "Page " & PageNum
If Err.Number 0 Then
MsgBox "Change the name of : " & Nsheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0
With Asheet
.Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
' If you want to make values of your formulas use this line also
' Nsheet.UsedRange.Value = Nsheet.UsedRange.Value
End If

RW = HPB.Location.Row
PageNum = PageNum + 1
Next HPB
Asheet.Select
Asheet.DisplayPageBreaks = False
Application.Goto Acell, True
Application.ScreenUpdating = True
End Sub

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

"bellamere" wrote in message ...
I have a large report in excel and I'd like to pull out a couple of pages to
send as an attachment. Can't find how to do this. Can someone help? Thanks:-)