PDA

View Full Version : [SOLVED] Filter data to pages based on number format



simora
09-13-2014, 05:04 PM
I have a worksheet with a column of book# formatted like 6032-11, 2349-5 , 7455-22 etc...etc..
I want to format each number to a separate page based on the value to the left of the dash.
The problem is that I don't know how many different book types I will have, and its possible that there's 4 0r 5 digits before the dash.

How can I format each book type ( The numbers to the left of the - )to a separate page and leave the original page untouched. After filtering, I want to automatically SUM the Column H of each filtered sheet and retain the page headings.
A VBA solution is preferred if possible.
SEE ATTACHED WORKSHEET

Any help appreciated.

Thanks

p45cal
09-14-2014, 07:38 AM
try:
Sub blah()
On Error GoTo here
Application.ScreenUpdating = False
With Sheets("Sheet1")
lr = .Cells(.Rows.Count, "F").End(xlUp).Row
Dim myFilters As New Collection
.Range("F2:F" & lr).TextToColumns Destination:=.Range("I2"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
On Error Resume Next
For Each cll In .Range("I2:I" & lr).Cells
myFilters.Add cll.Value, CStr(cll.Value)
Next cll
On Error GoTo here
.AutoFilterMode = False
With .Range("A1:I" & lr)
.AutoFilter
For Each filtr In myFilters
.AutoFilter Field:=9, Criteria1:=filtr
.Resize(, 8).Copy
With Sheets.Add(After:=Sheets(Sheets.Count))
.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("K1").FormulaR1C1 = "=SUM(R[1]C[-3]:R[" & .UsedRange.Rows.Count - 1 & "]C[-3])"
.Range("J1").Value = "PO Total --->"
.Columns("A:K").EntireColumn.AutoFit
.Cells(1).Select
.Name = "Prefix " & filtr
End With
Next filtr
Application.CutCopyMode = False
End With
.AutoFilterMode = False
.Range("I2:I" & lr).ClearContents
.Activate
.Range("A1").Select
End With
here:
Application.ScreenUpdating = True
End Sub

simora
09-14-2014, 10:59 PM
Thanks p45cal:

It worked great. No problems.
I had created a column with this formula =IF(F15>"",LEFT(F15,FIND("-",F15)-1),"") to get the numbers to the LEFT of the - ( The Prefix ), Then I was planning on doing the filter after that.
Your solution is much more elegant and precise.
Again.
Thank You.