The last thing to do to perfect this code is to insert subtotals for columns E and F and pagebreaks starting with cell A5. I need to do this in the new worksheets created as "MLW" and "NON. Any help would be appreciated. I have searched the posts, but I think I have a unique situation herel, as I have created new sheets from my original worksheet. I'm also a newbie -- so I apologize for my lack of knowledge.
Code
Sub NewSheets()
Dim wb As Workbook
Dim MLWcount As Long
Dim NONcount As Long
Dim i As Long
Dim wbNew As Workbook
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
Sheets("Logo").Visible = True
wb.Sheets("Logo").Copy
Set wbNew = ActiveWorkbook
wbNew.Sheets(1).Name = "NON"
wb.Sheets("Logo").Copy After:=wbNew.Sheets("NON")
wbNew.Sheets(2).Name = "MLW"
NONcount = 5
MLWcount = 5
For i = 2 To wb.Sheets("Info").Range("A65536").End(xlUp).Row
If Left(wb.Sheets("Info").Cells(i, 34).Value, 3) = "MLW" Then
wb.Sheets("Info").Rows(i).Copy wbNew.Sheets("MLW").Rows(MLWcount)
MLWcount = MLWcount + 1
Else
wb.Sheets("Info").Rows(i).Copy wbNew.Sheets("NON").Rows(NONcount)
NONcount = NONcount + 1
End If
Next
Sheets("Logo").Visible = False
End Sub
Display More