Hi There,
I have a macro where I am using WorksheetFunction.WorkDay function.
Below code works fine in my PC (that uses Office 365 Pro Plus) but giving an error when it is run in my colleagues PC (that uses Office 2013)
The error is Run-time error '1004': Unable to get the WorkDay property of the WorkSheetFunction class.
Could someone please help?
I have a macro where I am using WorksheetFunction.WorkDay function.
Below code works fine in my PC (that uses Office 365 Pro Plus) but giving an error when it is run in my colleagues PC (that uses Office 2013)
The error is Run-time error '1004': Unable to get the WorkDay property of the WorkSheetFunction class.
Could someone please help?
VBA Code:
Sub Copy_SEDOL_Report()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsHolidays As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant
Dim tdate As Date
Dim BDate As String
Dim Holiday(46) As Date
Holiday(0) = DateSerial(2020, 12, 25)
Holiday(1) = DateSerial(2020, 12, 26)
Holiday(2) = DateSerial(2020, 12, 28)
Holiday(3) = DateSerial(2021, 1, 1)
Holiday(4) = DateSerial(2021, 1, 2)
Holiday(5) = DateSerial(2021, 1, 4)
Holiday(6) = DateSerial(2021, 2, 1)
Holiday(7) = DateSerial(2021, 2, 6)
Holiday(8) = DateSerial(2021, 2, 8)
Holiday(9) = DateSerial(2021, 4, 2)
Holiday(10) = DateSerial(2021, 4, 5)
Holiday(11) = DateSerial(2021, 4, 25)
Holiday(12) = DateSerial(2021, 4, 26)
Holiday(13) = DateSerial(2021, 6, 7)
Holiday(14) = DateSerial(2021, 10, 25)
Holiday(15) = DateSerial(2021, 12, 25)
Holiday(16) = DateSerial(2021, 12, 26)
Holiday(17) = DateSerial(2021, 12, 27)
Holiday(18) = DateSerial(2021, 12, 28)
Holiday(19) = DateSerial(2022, 1, 1)
Holiday(20) = DateSerial(2022, 1, 2)
Holiday(21) = DateSerial(2022, 1, 2)
Holiday(22) = DateSerial(2022, 1, 3)
Holiday(23) = DateSerial(2022, 1, 4)
Holiday(24) = DateSerial(2022, 1, 31)
Holiday(25) = DateSerial(2022, 2, 6)
Holiday(26) = DateSerial(2022, 2, 7)
Holiday(27) = DateSerial(2022, 4, 15)
Holiday(28) = DateSerial(2022, 4, 18)
Holiday(29) = DateSerial(2022, 4, 25)
Holiday(30) = DateSerial(2022, 6, 6)
Holiday(31) = DateSerial(2022, 10, 24)
Holiday(32) = DateSerial(2022, 12, 25)
Holiday(33) = DateSerial(2022, 12, 26)
Holiday(34) = DateSerial(2022, 12, 27)
Holiday(35) = DateSerial(2023, 1, 1)
Holiday(36) = DateSerial(2023, 1, 2)
Holiday(37) = DateSerial(2023, 1, 3)
Holiday(38) = DateSerial(2023, 1, 30)
Holiday(39) = DateSerial(2023, 2, 6)
Holiday(40) = DateSerial(2023, 4, 7)
Holiday(41) = DateSerial(2023, 4, 10)
Holiday(42) = DateSerial(2023, 4, 25)
Holiday(43) = DateSerial(2023, 6, 5)
Holiday(44) = DateSerial(2023, 10, 23)
Holiday(45) = DateSerial(2023, 12, 25)
Holiday(46) = DateSerial(2023, 12, 26)
Set wsCopy = Workbooks("Cash Holdings.xlsm").Worksheets("NAM_SEDOL_Report")
Set wsDest = Workbooks("Cash Holdings.xlsm").Worksheets("HC_NAM_SEDOL_Report")
tdate = Date
BDate = Application.WorksheetFunction.WorkDay(tdate, -1, Holiday)
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
'wsDest.Range("A2:I" & lDestLastRow).ClearContents
wsDest.Range("A2:I200").ClearContents
'4. Copy & Paste Data
wsCopy.Range("A2:I" & lCopyLastRow).Copy
wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues
'Get date copied
wsDest.Range("C2:C" & lCopyLastRow) = Format(BDate, "YYYYMMDD")
End Sub
Attachments
Last edited by a moderator: