Macro help - Tab name changes daily

[jmkerzic]

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

 

[mole999]

would have thought activesheet would be the way to focus that

 

[sykes]

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

Private Sub Worksheet_Activate()
Dim str As String
str = MsgBox("This sheet's name is currently: " & Me.Name)
End Sub

 

[Steve_]

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.

 

[MoshiM]

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

 

[mikerickson]

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.

 

[AlphaFrog]

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.

dateToUse = Format(Now - Application.Max(Weekday(Now, vbTuesday) - 4, 1), "mm-dd-yyyy")

 

[Steve_]

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..

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

 

[jmkerzic]

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:

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
    [B]Sheets("All Vendors 07-09-2019").Sele[/B]ct
    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
    [B]Windows("All Vendors 07-09-2019").Activate[/B]
    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

 

[Steve_]

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.

    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.