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

n0tgirl

New Member
Joined
Oct 8, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
VBA 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.
VBA Code:
    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)
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?

VBA Code:
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
 
Upvote 0
Solution
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:
Rich (BB code):
 For Each ws In ActiveWorkbook.Worksheets
 
Last edited:
Upvote 0
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?

VBA Code:
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.
 
Upvote 0
You are very welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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