+ Reply to Thread
Results 1 to 3 of 3

Pass sheet name to Sub

Hybrid View

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Pass sheet name to Sub

    I have a Sub to sort sheets by due date (D:D)

    As each sheet is selected the sheetcode activate calls the sort routine.
    This works
    Sub Sdate(wsName As String)
    Dim ws1 As Worksheet
    Set ws1 = Sheets(wsName)
    Range("A3:J250").Select
    Sheets("April").Sort.SortFields.Clear
    Sheets("April").Sort.SortFields.Add Key:=Range("D3:D250"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Sheets("April").Sort
               .SetRange Range("A3:J250")
               .Header = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With
    End Sub
    if manually plug in the sheet name

    This doesn't
    Sub Sdate(wsName As String)
    Dim ws1 As Worksheet
    Set ws1 = Sheets(wsName)
    Range("A3:J250").Select
    Sheets(ws1).Sort.SortFields.Clear
    Sheets(ws1).Sort.SortFields.Add Key:=Range("D3:D250"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Sheets(ws1).Sort
               .SetRange Range("A3:J250")
               .Header = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With
    
    End Sub
    Called with
    Call Sdate("April")
    The immediate window does show ws1 = "April"

    Also how do you turn off the highlight after the range selection?

    Please point out my ignorance.
    thanks

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Pass sheet name to Sub

    It should just be ws1 not Sheets(ws1)
    ws1.Sort.SortFields.Clear
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Pass sheet name to Sub

    Thanks Norie - I do feel dumb.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1