Asterisk not working when trying to rename a worksheet

user_14030663 0
Viewed 52

I'm trying to sort through several workbooks that all have a main worksheet starting with the year '2020'

I'd like to simply loop through each workbook and change the name but the following code won't work:

Sheets("2020-*").Name = "MAIN"

However, the following code does work:

Sheets("2020-07-01 11.05.04").Name = "MAIN"

Here's the full code:

Sub dTa_dTb_fix()
'
' dTa_dTb_fix Macro

Dim FolderPath As String, path As String, count As Integer

FolderPath = "C:\Data_Analysis\7-Pass_EVAP"

path = FolderPath & "\2020-*.xlsx"

Filename = Dir(path)

Workbooks.Open (FolderPath & "\Combined_7.xlsx")

count = 2

Application.ScreenUpdating = False

Do While Filename <> ""

    Set ClosedBook = Workbooks.Open(Filename)
    Sheets("2020-*").Name = "MAIN"
    
    Worksheets("MAIN").ListObjects("Table1").ListColumns("dTa").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO OUTLET TEMP  °C]]-[@[EVAPORATOR PAO INLET TEMP  °C]])-[@[CONDENSER PAO INLET TEMP °C ]])"
    Worksheets("MAIN").ListObjects("Table1").ListColumns("dTb").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO INLET TEMP  °C]]-[@[EVAPORATOR PAO OUTLET TEMP  °C]])-[@[CONDENSER PAO OUTLET TEMP °C ]])"

    ClosedBook.Close SaveChanges:=False
    
    Filename = Dir()
    count = count + 1
Loop

Application.ScreenUpdating = True

    
End Sub

Can someone help please?

worksheet rename vba excel
1 Answers

Edited as per question revisions.

Added InStr() to search the contents of ActiveWorkbook.Worksheets(I).Name for the 2020- criteria.

InStr Arguments

The InStr VBA function accepts 4 arguments, 2 of which are optional:

InStr([Start], MainString, SubString, [Compare As VbCompareMethod])

src: https://wellsr.com/vba/2016/excel/use-vba-instr-to-test-if-string-contains-substring/

(untested)

Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer
     Dim FolderPath As String, path As String, count As Integer
     
     Application.ScreenUpdating = False

     ' not needed if FolderPath is a constant
     FolderPath = "C:\Data_Analysis\7-Pass_EVAP"  
     Workbooks.Open (FolderPath & "\Combined_7.xlsx")

     ' you could just use this
     Workbooks.Open ("C:\Data_Analysis\7-Pass_EVAP\Combined_7.xlsx")

     WS_Count = ActiveWorkbook.Worksheets.Count

     For I = 1 To WS_Count
        If InStr(1, ActiveWorkbook.Worksheets(I).Name , "2020-") <> 0 Then
          ActiveWorksheet.Name = "MAIN"
          Worksheets("MAIN").ListObjects("Table1").ListColumns("dTa").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO OUTLET TEMP  °C]]-[@[EVAPORATOR PAO INLET TEMP  °C]])-[@[CONDENSER PAO INLET TEMP °C ]])"
          Worksheets("MAIN").ListObjects("Table1").ListColumns("dTb").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO INLET TEMP  °C]]-[@[EVAPORATOR PAO OUTLET TEMP  °C]])-[@[CONDENSER PAO OUTLET TEMP °C ]])"
        End If
     Next I
     ClosedBook.Close SaveChanges:=False
     Application.ScreenUpdating = True

End Sub
user_41012100
Related