Insert page break at blank rows

[Miles]

I use a 3rd party software to export sales information into excel. I
would like to insert a page break or send data from each individual
person to its own worksheet. For example...I would like John Smith's
& Steve Jones' sales details to be put into new individual worksheets
or able to be printed out with page breaks where the "BLANK ROWs"
are. Notice, John Smith sold 3 different kinds of fruit while Steve
Jones has only sold 2 different fruits. I have played with SUBTOTALS
but have not had much success...

A B
1 Sales Rep Sales Totals
2 (BLANK ROW)
3 John Smith
4 Apples 20
5 Oranges 15
6 Pears 12
7 Total 47
8 (BLANK ROW)
9 Steve Jones
10 Apples 10
11 Oranges 15
12 Total 25
13 (BLANK ROW)

Thanks...

 

[Dave Peterson]

I'd keep the data in the same location. But I'd add a new column A.

Put "Name" in A1 (some header)
Then in A2, put this formula:
=IF(B1="","",A1)
And drag down as far as you need.

I ended up with something that looked like:

Name Sales Rep Sales Totals
Name
John Smith John Smith
John Smith Apples 20
John Smith Oranges 15
John Smith Pears 12
John Smith Total 47
John Smith
Steve Jones Steve Jones
Steve Jones Apples 10
Steve Jones Oranges 15
Steve Jones Total 25
Steve Jones

There was some "ugliness", but nothing that bothered me.

Then select column A
Data|Filter|autofilter.

You can exclude column A from the print range and just filter to show the data
for the name you want to print.

 

[Miles]

I was looking for something a little more automatic because I am
dealing with 100+ sales reps selling 25+ brands. I need the page
breaks/individual worksheets because each rep would need to see a
print out of their sales detail only. Dave, if I was dealing with a
smaller data set your solution would work great. Any other ideas??

 

[Dave Peterson]

First, I made a typo. This should go in A2:
=IF(B1="",B2,A1)

I would still use a formula like that--either manually or via code.

But if I needed to put the data in individual worksheets, I'd use something like
Ron de Bruin's EasyFilter addin or borrow some code from Debra Dalgleish:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[Dave Peterson]

Sorry.

I thought that you really wanted separate worksheets.

But if all you want is pagebreaks, you could use a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
.ResetAllPageBreaks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1").EntireColumn.Cells _
.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No blanks!"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.Row > 2 Then
'.HPageBreaks.Add Before:=myCell
.HPageBreaks.Add Before:=myCell.Offset(1, 0)
End If
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm