VBA to select last 2 sheets created that are named in mm-dd-yy format

[n0tgirl]

Hello,
I am trying to write some code that will select two sheets from an existing file and copy them into a new workbook. My sheets are named via code.

Dim dateToday As String
dateToday = Format(Date, "mm-dd-yy")
Sheets("Sheet1").Name = dateToday

This is the code that I tried, and I can see that my dateToday is "10-08-24" String and yesterdayDate is "10-07-24" also as string in my locals window. So that looks good to me.

    Dim ws As Worksheet
    Dim dateToday As String
    Dim yesterdayDate As String
    Dim newWorkbook As Workbook
   
    ' Get today's and yesterday's dates in "MM-DD-YY" format
    dateToday = Format(Date, "mm-dd-yy")
    yesterdayDate = Format(Application.Evaluate("=WORKDAY(TODAY(),-1)"), "mm-dd-yy")
  
    'loop through the sheets to select today and yesterday  
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = dateToday Then
        Set todaySheet = ws
        ElseIf ws.Name = yesterdayDate Then
        Set yesterdaySheet = ws
    End If
    Next ws
   
    ' Create a new workbook
    Set newWorkbook = Workbooks.Add

however when I try to run it, my todaySheet and yesterdaySheet are both empty and nothing is selected. What am I doing wrong? My sheets will always be today's and yesterday's (last business day so no weekends or holiday) and they will always be the last 2 sheets that were created right now they are sheet187 (10-07-24) and sheet188 (10-08-24)

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Assuming the two relevant sheets do exist in the workbook, would this work for you?

Sub Test()
  Dim dateToday As String
  Dim yesterdayDate As String
  
  ' Get today's and yesterday's dates in "MM-DD-YY" format
  dateToday = Format(Date, "mm-dd-yy")
  yesterdayDate = Format(Application.Evaluate("=WORKDAY(TODAY(),-1)"), "mm-dd-yy")
  
  ' Create a new workbook
  Sheets(Array(yesterdayDate, dateToday)).Copy
End Sub

 

[Alex Blakenburg]

If you are sure that the worksheets will always exist then Peter's code should work for you.
In terms of troubleshooting the code you had, ThisWorkbook refers to the workbook the code is in. Is the code in the same workbook as the sheets you are trying to copy ?
If not and the workbook with the sheets is the activesheet try using:

 For Each ws In ActiveWorkbook.Worksheets

 

[n0tgirl]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Assuming the two relevant sheets do exist in the workbook, would this work for you?

Sub Test()
  Dim dateToday As String
  Dim yesterdayDate As String
 
  ' Get today's and yesterday's dates in "MM-DD-YY" format
  dateToday = Format(Date, "mm-dd-yy")
  yesterdayDate = Format(Application.Evaluate("=WORKDAY(TODAY(),-1)"), "mm-dd-yy")
 
  ' Create a new workbook
  Sheets(Array(yesterdayDate, dateToday)).Copy
End Sub

This worked perfectly. Thank you so much. I appreciate the help and the assistance with the code tags.

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.