CODEWITHSUNDEEP
excelvba
Online Statements
Online Statements

Reputation: 27

Can I change date to Monday if date equals Saturday or Sunday in Excel VBA Macro

When I run my excel VBA macro it automatically copies a sheet called temp into a new sheet formatted "day of week-month-day-year" "ddd-MMM-dd-yyyy" It will name this new tab with tomorrows date. But when I run this on Friday or Saturday it will use Saturday or Sunday for the Date. When ran on Sunday it will use Monday which is good.

I have tried using code from this StackOverflow post and mix it into my code without any luck. Move next Dateadd to Monday if it falls on Saturday or Sunday Not exactly sure how to make it work with my code.

ThisWorkbook.Sheets("temp").Copy After:=Sheets(Sheets.Count)
ThisWorkbook.Sheets(Sheets.Count).Name = Format(Date + 1, "ddd-MMM-dd-yyyy")

I need it to always use Monday if ran on a Friday or Saturday.

Upvotes: 0

Views: 1251

Answers (3)

Prajod Francis
Prajod Francis

Reputation: 1

Sub Weekend_Dates()

    Dim k As Integer

    For k = 1 To k + 1

        If Weekday(Cells(k, 1).Value, vbMonday) = 1 Then
             Cells(k, 2).Value = Cells(k, 1)
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 2 Then
             Cells(k, 2).Value = Cells(k, 1)
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 3 Then
             Cells(k, 2).Value = Cells(k, 1)
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 4 Then
             Cells(k, 2).Value = Cells(k, 1)
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 5 Then
             Cells(k, 2).Value = Cells(k, 1)
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 6 Then
             Cells(k, 2).Value = Cells(k, 1) + 2
                Else
                Cells(k, 2).Value = Cells(k, 1) + 1
             
        End If

    Next k

End Sub

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

You could try:

Sub CopySheet()

    Dim strName As String

    If Format(Date, "dddd") = "Saturday" Then
        strName = Format(Date + 2, "ddd-MMM-dd-yyyy")
    ElseIf Format(Date, "dddd") = "Sunday" Then
        strName = Format(Date + 1, "ddd-MMM-dd-yyyy")
    End If

    ThisWorkbook.Sheets(Sheets.Count).name = strName

End Sub

Upvotes: 0

user11509084
user11509084

Reputation: 

ThisWorkbook.Sheets(Sheets.Count).Name = format(Application.WorksheetFunction.WorkDay(date, 1), "ddd-MMM-dd-yyyy")

Upvotes: 2

Related Questions