Macro help - Tab name changes daily

jmkerzic

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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
would have thought activesheet would be the way to focus that
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,701
Office Version
365
Platform
Windows
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
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
Office Version
2016
Platform
Windows
You can use the codename of the worksheet
EX: sheet33.range("X1")=
Ex: sheet33.name=
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,648
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,267
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:

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

jmkerzic

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

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,894
Messages
5,489,567
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top