Sort with variable sheet name?

[jmthompson]

Hey guys,
Can't figure this one out. The following series loops through all of the worksheets in my workbook. When it finds a sheets name that meets my criteria, it performs a number of actions. One of the actions I want to do is sort the data by key 1 column A and key 2 column B. I can't fugure out how to do this with my sheet name variable (ws). Any ideas?

Sub Cards()
Dim lastRow As Long
Dim c As Range
Dim ws As Worksheet
Dim myVar As String
Dim SiteCol As Range, cell As Range
Dim wsDest As Worksheet
Dim i As Long

For Each ws In ThisWorkbook.Sheets
       If ws.Name Like "##### Cardholders with MCC" Then
       ws.Cells.ClearContents
       myVar = Left(ws.Name, 5)
       Sheets("Q1 Cards").Select
       Set wsDest = ws
       i = wsDest.Cells(Rows.Count, 2).End(xlUp).Row
       If (i = 2) And (wsDest.Cells(2, 1) = "") Then i = 0
       Set SiteCol = Range("A2")
       Set SiteCol = Range(SiteCol, Cells(Rows.Count, SiteCol.Column).End(xlUp))
            For Each cell In SiteCol.Cells
                If cell.Value <> "" And cell.Offset(, 10) = myVar Then
                i = i + 1
                wsDest.Cells(i, 1).Resize(1, 17) = Array(cell.Offset(0, 1), cell.Offset(, 2), cell.Offset(0, 3), cell.Offset(0, 4), cell.Offset(0, 5), cell.Offset(0, 10), cell.Offset(0, 6), cell.Offset(0, 7), cell.Offset(0, 8), cell.Offset(0, 11), cell.Offset(0, 12), cell.Offset(0, 13), cell.Offset(0, 15), cell.Offset(0, 16), cell.Offset(0, 17), cell.Offset(0, 18), cell)
                End If
            Next
       ws.Range("A1").Value = "Last Name"
       ws.Range("B1").Value = "First Name"
       ws.Range("C1").Value = "Acct Number"
       ws.Range("D1").Value = "Single Limit"
       ws.Range("E1").Value = "Mo Limit"
       ws.Range("F1").Value = "BU"
       ws.Range("G1").Value = "MCC1"
       ws.Range("H1").Value = "MCC2"
       ws.Range("I1").Value = "MCC3"
       ws.Range("J1").Value = "L1-L2-Proj"
       ws.Range("K1").Value = "Dept"
       ws.Range("L1").Value = "GL"
       ws.Range("M1").Value = "PNet Access"
       ws.Range("N1").Value = "COM Access"
       ws.Range("O1").Value = "ESP Access"
       ws.Range("P1").Value = "Admin Access"
       ws.Range("Q1").Value = "Status"
       ws.Range("A1:Q1").Font.Bold = True
       ws.Columns.AutoFit
       ws.Range("D:E").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
       lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.PageSetup.PrintArea = "$A$1:$Q$" & lastRow
        With ws.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        End With
       End If

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add _
        Key:=Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ws.Sort.SortFields.Add _
        Key:=Range("B2:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:Q" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next ws
End Sub

 

[jvanbonn]

Should you get rid of the ##### like so?

If ws.Name Like " Cardholders with MCC" Then

or

If ws.Name Like ##### & " Cardholders with MCC" Then

 

[jmthompson]

Thanks for responding! That part of the code is working fine.

What I need to know is how to incorporate ws into the Sort code. I really don't want to break my loop to sort.

 

[jvanbonn]

What's the purpose of this?

ws.Cells.ClearContents

It appears to me that you clear the entire worksheet, but then set wsDest to ws, which is blank.

With ws and wsDest blank, how do you initialize i ?

i = wsDest.Cells(Rows.Count, 2).End(xlUp).Row

And if these sheets are blank, what is there to sort?

Sorry, I'm not much of a help.

 

[jvanbonn]

Is there other code that fills cells ("A2:Q" & lastRow)?

 

[jmthompson]

Figured it out, I now activate ws before the sort and sort using activesheet references.

Here's my final code and what it does

Sub Cards()
Dim lastRow As Long
Dim c As Range
Dim ws As Worksheet
Dim myVar As String
Dim SiteCol As Range, cell As Range
Dim wsDest As Worksheet
Dim i As Long
'look through workbook and if worksheet name is 5 numbers followed by " Cardholders with MCC", this becomes ws
For Each ws In ThisWorkbook.Sheets
       If ws.Name Like "##### Cardholders with MCC" Then
'remove all data from ws
       ws.Cells.ClearContents
       myVar = Left(ws.Name, 5)
       Sheets("Q1 Cards").Select
       Set wsDest = ws
       i = wsDest.Cells(Rows.Count, 2).End(xlUp).Row
       If (i = 2) And (wsDest.Cells(2, 1) = "") Then i = 0
       Set SiteCol = Range("A2")
       Set SiteCol = Range(SiteCol, Cells(Rows.Count, SiteCol.Column).End(xlUp))
'go to my sheet Q1 cards, for each row that isn't blank and has a cell that matches the 5 numbers in my destination worksheet (ws) name, copy select cells and place in my destination worksheet (ws).
            For Each cell In SiteCol.Cells
                If cell.Value <> "" And cell.Offset(, 10) = myVar Then
                i = i + 1
                wsDest.Cells(i, 1).Resize(1, 17) = Array(cell.Offset(0, 1), cell.Offset(, 2), cell.Offset(0, 3), cell.Offset(0, 4), cell.Offset(0, 5), cell.Offset(0, 10), cell.Offset(0, 6), cell.Offset(0, 7), cell.Offset(0, 8), cell.Offset(0, 11), cell.Offset(0, 12), cell.Offset(0, 13), cell.Offset(0, 15), cell.Offset(0, 16), cell.Offset(0, 17), cell.Offset(0, 18), cell)
                End If
            Next

'add headers to ws
       ws.Range("A1").Value = "Last Name"
       ws.Range("B1").Value = "First Name"
       ws.Range("C1").Value = "Acct Number"
       ws.Range("D1").Value = "Single Limit"
       ws.Range("E1").Value = "Mo Limit"
       ws.Range("F1").Value = "BU"
       ws.Range("G1").Value = "MCC1"
       ws.Range("H1").Value = "MCC2"
       ws.Range("I1").Value = "MCC3"
       ws.Range("J1").Value = "L1-L2-Proj"
       ws.Range("K1").Value = "Dept"
       ws.Range("L1").Value = "GL"
       ws.Range("M1").Value = "PNet Access"
       ws.Range("N1").Value = "COM Access"
       ws.Range("O1").Value = "ESP Access"
       ws.Range("P1").Value = "Admin Access"
       ws.Range("Q1").Value = "Status"
'add some formatting to ws
       ws.Range("A1:Q1").Font.Bold = True
       ws.Columns.AutoFit
       ws.Range("D:E").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
       lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.PageSetup.PrintArea = "$A$1:$Q$" & lastRow
        With ws.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        End With
'sort ws       
    ws.Activate
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add _
        Key:=Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add _
        Key:=Range("B2:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:Q" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
'move to next worksheet in workbook
Next ws
End Sub

 

[jvanbonn]

Nice Code!

I think I learned more from you than helped you. Thanks.

I wonder why you had to activate ws to sort it?