How to skip holiday while pulling data from past date.

[Altamash]

Need you help !


I am using below codes to copy data from another closed workbook to my macro workbook.
below is the code.Date in file name always changes other thing constant.


If i am running this report on Monday 16.08.21 and want data from date 12.08.21 thrusday.


Also if running report on tuesday and want data from 13.08.21 friday.
and if running report on wednesday and want data from Monday 16.08.21.


what should we do in this case.


also if i am running report and above dates are holiday and there is no file saved then how to skip.that date
please advise.



Sub FetchValidationPolicy()

Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Dim PreviousDate As String
Dim k As Integer

Set currentworkbook = ThisWorkbook

k = Weekday(Date, vbUseSystemDayOfWeek)

If k = 1 Then
PreviousDate = Format(Date - 3, "dd.mm.yyyy")
Else
PreviousDate = Format(Date - 2, "dd.mm.yyyy")
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

 

[Crystalyzer]

Try this

Sub FetchValidationPolicy()

    Dim sourceworkbook As Workbook
    Dim currentworkbook As Workbook
    Dim PreviousDate As String
    Dim k As Integer
    Dim PrevDate As Date
    Const Holidays As String = "44381,44433,44445"  'numeric values of holidays, you will have to change these values
    Dim arr As Variant
    
    arr = Split(Holidays, ",")
     
    Set currentworkbook = ThisWorkbook
    
    k = Weekday(Date, vbUseSystemDayOfWeek)
    
    If k = 1 Then
        PreviousDate = Format(Date - 3, "dd.mm.yyyy")
        PrevDate = CDate(Date - 3)
    Else
        PreviousDate = Format(Date - 2, "dd.mm.yyyy")
        PrevDate = CDate(Date - 2)
    End If
    
    If WorksheetFunction.WorkDay(PrevDate - 1, 1, arr) = PrevDate Then
        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 If
End Sub

 

[Altamash]

Hi There,

Thanks, but i can't even run the codes.
Nothing is happening after running.