autosort in a loop in multiple worksheet

[ysherriff]

hello all,

I have a template worksheet that generates multiple worksheets based on a defined list of names. I would like to know how to autosort in the loop. I attempted but i am running into issues. Below is the code and the attached file.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range



Set MyRange = Sheets("Control").Range("Shorten_DSM_List")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Application.ScreenUpdating = False

For Each MyCell In MyRange

Sheets("Template").Copy After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Sheet4.Activate ' activates alignment sheet
Range("PSR_GLC_LIST_START_CELL").Select 'select first cell in PSR_GLC List sheet

If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
End If

ActiveSheet.Range("PSR_GLC_LIST_TABLE_RANGE").AutoFilter Field:=1, Criteria1:=MyCell 'filters data in alignment sheet
ActiveSheet.Range("A2").CurrentRegion.Select
Selection.Offset(1).Resize(Selection.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy 'copies the selected data
Sheets(MyCell.Value).Activate 'call the new sheet
Range("START_CELL").Select 'select the cell to start paste
'Sheets(MyCell.Value).Paste 'paste the new value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("DSM_NAME") = MyCell.Value



Range("TABLE_SORT_RANGE").Sort.SortFields.Add Key:=Range("E9:E95" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Range("TABLE_SORT_RANGE").Sort.SortFields.Add Key:=Range("D9:D95" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveSheet.UsedRange.Columns("b:f").AutoFit 'AutoFit the column width

Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Range("ADMIT_START_CELL").Activate

Next MyCell

Sheet1.Select
Sheets("TEMPLATE").Visible = False 'hide sheet
Application.ScreenUpdating = True
End Sub

 

[ysherriff]

I forgot to mention this is a multiple criteria sort. I am sorting first by column e9:e95 and then second criteria is d9:d95

 

[NARAYANK991]

Hi ,

I am not sure what you want , but see your file now.

Narayan

 

[ysherriff]

Thank you Narayank. That is exactly what I wanted. I wanted to sort by multiple criteria......

You are the best!!!

 

[ysherriff]

Narayank,

Can you look at the file again? I ran a macro to consolidate multiple sheet. The name of the macro is Consolidatesheet and for some reason there is a seperation between the rows. Can you see what i am missing. The consolidation should be on the "summary" sheet.

I will also look at it too.

 

[NARAYANK991]

Hi ,

See your file now. The problem was the header portion , which covered 8 rows in every sheet.

Narayan

 

[ysherriff]

Thank you very very much!!!!!!