How to deal with Page Number without using the footer dialog box

  • Thread starter Thread starter joelimbox
  • Start date Start date
J

joelimbox

I have a question regarding EXCEL software.

For the long list of data in Excel wksheet (let's say 1000 lines wit
10 departments), I have inserted manually the page-breaks so as t
divide the whole lump of data up by different departments (i.e. ne
department will start fresh on another page/ ie. Department B wil
start on new page)

But, I have a problem. I cannot put in the page numbers. If I use th
normal way to insert the page number, the numbers will be in series.

What I want is, if the next page is a new department, then the pag
number should start with ‘1’ and not continue from previous pag
number.

Can I do that
 
Another way is to apply Data|filter|autofilter and just cycle through the
department values in that filter.

You could even have a macro that does it.

First remove your manual page breaks (else you'll get extra pages when you
print).

Then try this:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim myCell As Range
Dim RngF As Range
Dim ExistingFilterRng As Range
Dim FilterColumnWithDept As Long

Set curWks = Worksheets("sheet1")
FilterColumnWithDept = 1 'first column in the autofiltered data

With curWks
If .AutoFilterMode = False Then
MsgBox "Please apply Data|Filter|Autofilter"
Exit Sub
End If

Set ExistingFilterRng = .AutoFilter.Range

With ExistingFilterRng
.Columns(FilterColumnWithDept).AdvancedFilter _
Action:=xlFilterInPlace, Unique:=True
Set RngF = Nothing
On Error GoTo 0
Set RngF = .Columns(FilterColumnWithDept).Offset(1, 0) _
.Resize(.Rows.Count - 1).Cells _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

If RngF Is Nothing Then
'shouldn't happen
MsgBox "something bad happened"
Exit Sub
End If

For Each myCell In RngF.Cells
ExistingFilterRng.Columns(FilterColumnWithDept) _
.AutoFilter Field:=1, Criteria1:=myCell.Value
.PrintOut preview:=True 'used for testing only
Next myCell

.AutoFilterMode = False

ExistingFilterRng.AutoFilter
End With
End Sub

If your department number isn't in the leftmost column of your filtered data,
then change this line:

FilterColumnWithDept = 1 'first column in the autofiltered data
to match the column in the filtered range.

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