Macro for update attendance sheet (shhet1) when update leave dates in Sheet2

[manissinha5]

Hi All

Macro for update attendance sheet (shhet1) when update leave dates in Sheet2

please help me to write code for this auto update sheet1 date when i update leave date in sheet2

Thanks
Manish

[Winon]

Hello manissinha5,

Your requirement is not quite clear. Please upload a sample Workbook, showing us a "before" and "after", i.e. which Cells do you need to update in Sheet 1.
Also Sheet 1 contains a Date range for August 2019 whilst Sheet 2 shows dates for November 2019, which are not available in Sheet 1. Do you want to have the Dates in Sheet 1 to be overwritten with changes made on Sheet 2?

Regards.

[manissinha5]

Hi Winon

when I update the leave dates i.e 15/11/2019 to 20/11/2019 in sheet2 the "PL" should automatic marked in sheet1 on 15,16,17,18,19,20 excluding week off on sat sun ( if Sat sun in between those date shoul marked "WO"
hope this will help you to write code

regards

[JeteMc]

Perhaps this formula based proposal will help.
This proposal makes some changes to the data setup.
1. The range A2:E12 on Sheet 2 is converted to a table.
2. The values in cells D3:E12 are changed from text to dates using the Text to Columns feature.
3. On Sheet 1 the range D19:AH31 is populated using:

Please Login or Register  to view this content.

Note that D19:AH31 may be moved and/or hidden for aesthetic purposes.
4. The range D5:AH17 is populated using: =IF(D$1="","",IF(AND(D19<>"",WEEKDAY(D$1,2)>=6),"WO",D19))
5. D2 is manually populated 11/1/2019
6. E2:AH2 are populated using: =IF(MONTH(D1+1)=MONTH($D1),D1+1,"")
7. D3:AH3 are populated using: =D1 and custom formatted ddd
Let us know if you have any questions.