Excel VBA - Q&A

Sub output_sheets()
Dim rng As Range
Dim myRng As Range

With main
'clear filter on main tab and clear temp sheet
If main.FilterMode Then main.ShowAllData

Set rng = .Range("B1:B" & .Range("B" & Rows.Count).End(xlUp).Row)

rng.Copy temp.Range("A1")

temp.Range("A1:A" & temp.Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates 1, xlYes

Set rng = temp.Range("A2:A" & temp.Range("A" & Rows.Count).End(xlUp).Row)

For Each State In rng
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = UCase(State)

'filtro
main.Range("A1:C" & main.Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 2, State

.AutoFilter.Sort.SortFields.Add Key:=Range( _
"A1:A13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Set myRng = main.Range("A1:C" & main.Range("A" & Rows.Count).End(xlUp).Row)
myRng.SpecialCells(xlCellTypeVisible).Copy Sheets(State.Value).Range("a1")

Next State
End With


End Sub