How to skip monday to fetch data from another workbook if particular date is holiday .

[Altamash]

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

 

[jorismoerings]

Hi,

I can't fully comprehend what your code does.

It creates three variables, opens a workbook, copies a fixed range (B2:B500) and copies onto the validation sheet of current workbook.
Your question is:

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.

But your code doesn't include a count. So can't answer that.
The part of holidays. VBA doesn't have the regular worksheet functions like WORKDAY or NETWORKDAYS however they can be used by using WorkSheetFunction.WorkDay.

So basically: you need to provide better information on what the code needs to do.