Macro help - Tab name changes daily
Results 1 to 10 of 10

Thread: Macro help - Tab name changes daily

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro help - Tab name changes daily

    I use a macro on spreadsheet and the tab name changes every day to current day. I need to modify my macro so I do not have to change the date in it everyday.

    This is what I have so far. Not sure is this the correct way to do it.

    '
    Dim dateToUse As String
    Dim dateOverride As String
    Dim worksheetName As String

    Select Case Weekday(Now)
    ' Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7
    Case 1
    ' Get Friday's date(subtract 2 days from today)
    dateToUse = Format(DateAdd("d", -2, CDate(Now)), "mm-dd-yyyy")
    Case 2
    ' Get Friday's date(subtract 3 days from today)
    dateToUse = Format(DateAdd("d", -3, CDate(Now)), "mm-dd-yyyy")
    Case 3, 4, 5, 6, 7
    ' Get Previous days date
    dateToUse = Format(DateAdd("d", -1, CDate(Now)), "mm-dd-yyyy")
    End Select

    dateOverride = ""
    If dateOverride = "" Then
    worksheetName = dateToUse
    Else
    worksheetName = dateOverride
    End If

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,819
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro help - Tab name changes daily

    would have thought activesheet would be the way to focus that
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    Can't you just put your code into the sheet's own code module, then use the "Me" keyword to refer to the sheet? That way it'll work, even when the sheet's name changes
    Code:
    Private Sub Worksheet_Activate()
    Dim str As String
    str = MsgBox("This sheet's name is currently: " & Me.Name)
    End Sub
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    So, can you give examples of what you would expect your tab names to look like? And when do you want the name of the tab to be updated? From what I see of your code, it should be working fine, other than the name change not actually being applied yet.
    Last edited by Fluff; Jul 10th, 2019 at 11:16 AM. Reason: removed full quote

  5. #5
    Board Regular
    Join Date
    Jan 2018
    Posts
    186
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    You can use the codename of the worksheet
    EX: sheet33.range("X1")=
    Ex: sheet33.name=

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,467
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro help - Tab name changes daily

    Some days you have to change the name of the sheet. Some days not.
    It almost sounds like a macro would be more work than doing it manually.

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,002
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Macro help - Tab name changes daily

    I think we are all confused as to what your actual question is (or maybe it's just me).


    This should do the same as your Select Case code block.

    Code:
    dateToUse = Format(Now - Application.Max(Weekday(Now, vbTuesday) - 4, 1), "mm-dd-yyyy")
    Last edited by AlphaFrog; Jul 10th, 2019 at 11:15 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    So based off of your explanation, here is my offered solution...

    FIRST, go into your code, on the left side in the object explorer, find your sheet that will have its name updated daily.
    Click it. In the properties below, you will see the top property is (Name). I want you to set this to 'Target'

    NOW, go to the 'ThisWorkbook' object and post the following code..


    Code:
    Private Sub Workbook_Open()
        For Each sh In Worksheets
            If sh.CodeName = "Target" Then
                Exit For
            End If
        Next sh
        Dim dateToUse As String
        Select Case Weekday(Now) ' Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7
            Case 1               ' Get Friday's date(subtract 2 days from today)
                dateToUse = Format(DateAdd("d", -2, CDate(Now)), "mm-dd-yyyy")
            Case 2               ' Get Friday's date(subtract 3 days from today)
                dateToUse = Format(DateAdd("d", -3, CDate(Now)), "mm-dd-yyyy")
            Case 3, 4, 5, 6, 7   ' Get Previous days date
                dateToUse = Format(DateAdd("d", -1, CDate(Now)), "mm-dd-yyyy")
        End Select
        sh.Name = dateToUse
    End Sub
    I THINK this is the result you are looking for
    Last edited by Fluff; Jul 10th, 2019 at 11:17 AM. Reason: Removed full quote

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    I have put in bold the lines that I have to manually change every day. The tab name is All Vendors 07-09-2019 I change the date manually on the 2 lines, just want it put the name of the tab there.

    Here is the entire Macro that I use:

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    
    
    '
     Dim dateToUse As String
        Dim dateOverride As String
        Dim worksheetName As String
       
        Select Case Weekday(Now)
            ' Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7
            Case 1
                ' Get Friday's date(subtract 2 days from today)
                dateToUse = Format(DateAdd("d", -2, CDate(Now)), "mm-dd-yyyy")
            Case 2
                ' Get Friday's date(subtract 3 days from today)
                dateToUse = Format(DateAdd("d", -3, CDate(Now)), "mm-dd-yyyy")
            Case 3, 4, 5, 6, 7
                ' Get Previous days date
                dateToUse = Format(DateAdd("d", -1, CDate(Now)), "mm-dd-yyyy")
        End Select
       
        dateOverride = ""
        If dateOverride = "" Then
            worksheetName = dateToUse
        Else
            worksheetName = dateOverride
        End If '
    
    '
     Dim numOfRows As Integer
        Dim numOfCols As Integer
        
        ' Get the number of rows in the spreadsheet
        numOfRows = Worksheets(1).UsedRange.Rows.Count
        numOfCols = Worksheets(1).UsedRange.Columns.Count
        
        ' Loop through each row and convert UPCs to 12 digit UPCs
        Dim rowIndex As Integer
        Dim colIndex As Integer
        Dim currentCell As String
        
        ' Loop through each row
        For rowIndex = 2 To numOfRows
            ' Loop through each column
            For colIndex = 1 To numOfCols
                ' Reset the currentUPC variable
                currentCell = ""
                
                ' If the header for the current column is CreditDebitNum, InvoiceNum, PONum then convert the cell to text
                ' If the header for the current column starts with UPC and the current cell contains a value check to see if it needs to be fixed
                If (Cells(1, colIndex).Value = "CreditDebitNum" Or Cells(1, colIndex).Value = "InvoiceNum" Or Cells(1, colIndex).Value = "PONum") And Trim(Cells(rowIndex, colIndex).Value) <> "" Then
                    ' Copy the current cell content to the clipboard
                    currentCell = Trim(Cells(rowIndex, colIndex).Value)
                    
                    ' Change the format of the current cell to Text
                    Cells(rowIndex, colIndex).NumberFormat = "@"
                        
                    ' Update the current cell with the fixed UPC
                    Cells(rowIndex, colIndex).Value = currentCell
                ElseIf Left(Cells(1, colIndex).Value, 3) = "UPC" And Trim(Cells(rowIndex, colIndex).Value) <> "" Then
                    ' If the length of the current cell is greater than 10 then this should be a 12 digit UPC, else leave it alone
                    If Len(Trim(Cells(rowIndex, colIndex).Value)) > 10 Then
                        ' Update the current cell to a 12 digit UPC and save it to a temporary variable
                        currentCell = Right("000000000000" & Trim(Cells(rowIndex, colIndex).Value), 12)
                        
                        ' Change the format of the current cell to Text
                        Cells(rowIndex, colIndex).NumberFormat = "@"
                        
                        ' Update the current cell with the fixed UPC
                        Cells(rowIndex, colIndex).Value = currentCell
                    End If
                End If
            Next colIndex
        Next rowIndex
      
      '
        Cells.Select
            Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=RIGHT($C1,1)=""B"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .Color = 65280
            .TintAndShade = 0
        End With
        Cells.Select
            Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=LEFT($C1,1)=""R"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .Color = 13882323
            .TintAndShade = 0
             End With
        Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=LEFT($C1,1)=""V"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .Color = 9419919
            .TintAndShade = 0
             End With
        Selection.FormatConditions(1).StopIfTrue = False
        Cells.Select
            Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=LEFT($C1,1)=""T"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .Color = 13408767
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Selection.FormatConditions(1).StopIfTrue = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=COUNTIF(1:1,""*9m*"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select
        Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=RIGHT($C1,1)=""c"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .Color = 16776960
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
            Cells.Select
        Range("A352").Activate
        Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
            "=AND(LEFT($C1,1)=""R"",RIGHT($C1,1)=""B"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.399945066682943
        End With
          
        
        ' Autofit all the columns in the worksheet
        Range(Columns(1), Columns(numOfCols)).AutoFit
            Sheets.add After:=ActiveSheet
        Sheets("All Vendors 07-09-2019").Select
        Cells.Select
        Selection.Copy
        Sheets("Sheet1").Select
        ActiveSheet.Paste
      Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
        Range("A3").Select
        Columns("E:F").Select
        Range("F1").Activate
        Selection.NumberFormat = "0.00"
        Range("F4").Select
        Columns("g:h").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Windows("PERSONAL.XLSB").Activate
        Range("H2:I2").Select
        Selection.Copy
        Windows("All Vendors 07-09-2019").Activate
        Range("G2").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("G2:H4000")
        Range("G2:H4000").Select
        Columns("G:G").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("H:H").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Cells.Select
        ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.add Key:=Range("c2:c4000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("sheet1").Sort
            .SetRange Range("A1:AQ4000")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Last edited by Fluff; Jul 10th, 2019 at 11:18 AM. Reason: code tags

  10. #10
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help - Tab name changes daily

    See my previously reply. I am certain that is what you need

    However, you need to change those references to the sheet name and start calling by the sheet codename (The thing I asked you to set to 'Target'). Once you locate the codename, you can lock on to the sheet as an object and its daily name change will not matter anymore.

    Code:
        For Each sh In Worksheets
            If sh.CodeName = "Target" Then
                Exit For
            End If
        Next sh
    If you do this, everywhere else in your code you can reference that sheet by using 'sh', no matter what its name was changed to by the code.
    Last edited by Steve_; Jul 10th, 2019 at 11:23 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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