Results 1 to 2 of 2

Thread: Moving sheet...

  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Moving sheet...

    Hi Guys,

    I want to move the sheet based on it's name. I have "#" in front of the already closed accounts, each representing 1 account. So the file has "#2, #4, #9, #10, #11, #12, #13 & #17" at this time.
    I created the vba to rename the other sheets when the time comes to include the "#" and works well. What I would need some help with is the following:

    How do I go about finding the correct place to move the sheet to?
    Lets say for this example that account "8" just became "#8".
    What should I use to identify the spot between "#4" and "#9"?

    Code:
    '****************************************************
    'Sheet Re-Name...
    '****************************************************
    Sub AutoSheetName()
        MsgBox "ActiveSheet Name: " & ActiveSheet.Name
        shtName = ActiveSheet.Name                          'get ActiveSheet.Name
        shtName = Replace(shtName, "+", "")                 'delete +
        shtName = Replace(shtName, "@", "")                 'delete @
        shtName = Replace(shtName, "-", "")                 'delete -
        shtName = Replace(shtName, "#", "")                 'delete #
        RSI = Application.WorksheetFunction.Sum(Range("Z3:Z47"))
        SellDate = Cells(31, "B")
        
        If SellDate > 0 Then                                '#sheetName if sold
            ActiveSheet.Name = "#" + shtName
            ActiveSheet.Move after:=Worksheets()
        Else
            If RSI > 0 Then                                 'sheetName+ if there is RSI credit present
                ActiveSheet.Name = shtName + "+"
                Exit Sub
            End If
            For I = 3 To 47 Step 1
            
                If Cells(I, "J") = "Locked" Then            '+sheetName if items are locked
                    ActiveSheet.Name = "+" + shtName
                End If
                '--------------------------------------------
                If Cells(I, "J") = "" Then Exit Sub         'exit at first empty cell
                '--------------------------------------------
                If Cells(I, "J") = "Giftable" Then          'sheetName if giftable item is present
                    ActiveSheet.Name = shtName
                    Exit Sub
                End If
            
            Next I
        End If
    End Sub
    Thanks for looking
    Ferenc

  2. #2
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Cracked it already!

    Code:
        If SellDate > 0 Then                                '#sheetName if sold        Application.EnableEvents = False
            For Each shtNTH In ThisWorkbook.Sheets
                Application.EnableEvents = False
                shtName2 = shtNTH.Name                      'get ActiveSheet.Name
                If Left(shtName2, 1) = "#" Then
                    shtName2 = Replace(shtName2, "#", "")   'delete #
                    If shtName2 > shtName Then
                        ActiveSheet.Move Before:=shtNTH
                        ActiveSheet.Name = "#" + shtName
                        Exit For
                    End If
                End If
            Next
        Else

Posting Permissions

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