Hi There,
i use below code to fetch data from another workbook.
If Monday is holiday then which code is to use so that the code skip monday and do not count monday.
Also future date is holiday i.e, 10/08/2021, then how to make a lost of holidays in VBA code.
please advise.
Option Explicit
Sub FetchValidationPolicy()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Dim PreviousDate As String
Dim k As Integer
Set currentworkbook = ThisWorkbook
k = Weekday(Date, vbMonday)
If k = 1 Then 'Check if today is Monday'
PreviousDate = Format(Date - 4, "dd.mm.yyyy") 'returns the date of previous Thrusday'
Else
PreviousDate = Format(Date - 1, "dd.mm.yyyy") 'returns the date of previous day'
End If
If k = 2 Then 'Check if today is Tuesday'
PreviousDate = Format(Date - 3, "dd.mm.yyyy") 'returns the date of previous Friday'
End If
Set sourceworkbook = Workbooks.Open("Q:\Financial_Operations\Finance Operations Team\Internal\45HC_General\HC.07 Refund Team\HC.07.08 Diary Review & Updates\Refund Update & Diary Review " & PreviousDate & ".xlsx")
sourceworkbook.Worksheets("Vadliation List").Range("B2:B500").Copy
currentworkbook.Activate
currentworkbook.Worksheets("Validation").Activate
currentworkbook.Worksheets("Validation").Cells(2, 1).Select
ActiveSheet.Paste
sourceworkbook.Close
End Sub
i use below code to fetch data from another workbook.
If Monday is holiday then which code is to use so that the code skip monday and do not count monday.
Also future date is holiday i.e, 10/08/2021, then how to make a lost of holidays in VBA code.
please advise.
Option Explicit
Sub FetchValidationPolicy()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Dim PreviousDate As String
Dim k As Integer
Set currentworkbook = ThisWorkbook
k = Weekday(Date, vbMonday)
If k = 1 Then 'Check if today is Monday'
PreviousDate = Format(Date - 4, "dd.mm.yyyy") 'returns the date of previous Thrusday'
Else
PreviousDate = Format(Date - 1, "dd.mm.yyyy") 'returns the date of previous day'
End If
If k = 2 Then 'Check if today is Tuesday'
PreviousDate = Format(Date - 3, "dd.mm.yyyy") 'returns the date of previous Friday'
End If
Set sourceworkbook = Workbooks.Open("Q:\Financial_Operations\Finance Operations Team\Internal\45HC_General\HC.07 Refund Team\HC.07.08 Diary Review & Updates\Refund Update & Diary Review " & PreviousDate & ".xlsx")
sourceworkbook.Worksheets("Vadliation List").Range("B2:B500").Copy
currentworkbook.Activate
currentworkbook.Worksheets("Validation").Activate
currentworkbook.Worksheets("Validation").Cells(2, 1).Select
ActiveSheet.Paste
sourceworkbook.Close
End Sub