Adding Page breaks determined on random values.

[panda4444]

hi!

First let me thank you for all your help!

I trying to record a macro and having some trouble with page breaks.

When I import from a external program, Column A will have three different sets of order numbers. For example, lets say the three values are 111, 222, 333. And, there may be 4 rows of 111, 10 rows of 222, or 22 rows of 333. (it always changes, when I import)

Is there a way to tell excel page break when a value ends and a other begins, then move those specific rows to a new worksheet? CAn that be programmed into a macro? :D

BYE!
Panda :wink:

 

[Andrew Poulsom]

Have a look here:

http://216.92.17.166/board2/viewtopic.php?t=51064

 

[panda4444]

looks great but when I run it it say: "Subscription is out of range" on the bulging line below.


Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Dim ShNew As Worksheet
Application.ScreenUpdating = False
' *** Change Sheet name to suit ***
Set Sh = Worksheets("project")
Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("A1:L" & Sh.Range("A65536").End(xlUp).Row)
For Each Item In List
Set ShNew = Worksheets.Add
ShNew.Name = Item
Rng.AutoFilter Field:=1, Criteria1:=Item
Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
Rng.AutoFilter
Next Item
Sh.Activate
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Do you have a worksheet named "project"? If not, you need to change this name to the whatever the name of your sheet is.