Macro help - Tab name changes daily

jmkerzic

New Member
Joined
Jul 5, 2019
Messages
31
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
would have thought activesheet would be the way to focus that
 
Upvote 0
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
 
Upvote 0
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 a moderator:
Upvote 0
You can use the codename of the worksheet
EX: sheet33.range("X1")=
Ex: sheet33.name=
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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 a moderator:
Upvote 0
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
    [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
 
Last edited by a moderator:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top