Good day, Gurus. There have been several changes to the forum since I was here last. Most notabley applying "Code Tags" to show code. I checked the FAQ before posting, but it didn't tell me how to apply the code tags. I hope an Admin will tell me how to do this, and I will edit my messagebelow.
The code below successfully separates my "Master" worksheet into different sheets depending on the entry in column 14.
Sub Seperate_Struct_vs_Nonstruct()
Dim rng2 As Range, rng As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With Sheets("MASTER")
.AutoFilterMode = False
End With
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveSheet.Name = "MASTER"
ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Add Key:= _
Range("N1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("MASTER").Sort
.SetRange Range("A1:V" & Cells(Rows.Count, "A").End(xlUp).Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With Sheets("MASTER")
Sheets.Add().Name = "Temp"
.Range("N1", .Range("N1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
Set rng = Sheets("Temp").Range("A2", Sheets("Temp").Range("A2").End(xlDown))
For Each rng2 In rng
.Range("A1").CurrentRegion.AutoFilter Field:=14, Criteria1:=rng2
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
.AutoFilter.Range.Copy ws.Range("A1")
ws.Name = rng2
.Range("A1").CurrentRegion.AutoFilter Field:=14
Next rng2
Sheets("Temp").Delete
.AutoFilterMode = False
End With
Sheets("MASTER").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("A1").Select
End Sub
I would like to add code that applies the following additional formatting to each sheet, but wasn't sure where to add it or the correect syntax, since I assumed it would be undert the "with sheets" statement.
Sub Macro1()
Rows("1:1").Copy 'Copies row 1 from the Master worksheet.
Sheets("N").Select 'This is the sheet that was just created.
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False 'Copies the column width to tow 1.
ActiveSheet.Paste
Range("E2").Select
ActiveWindow.FreezePanes = True 'Freezes panes on the selected sheet.
Range("A1").Select 'Selects range A1 on the active sheet, then moves on to the next sheet.
End Sub
Thanks in advance for any help you can offer.
Bookmarks