Need VBA help to copy attendance status into calender table
Dear Experts
I have master sheet with emp name and emp date of leave
i want fill leave status across range of calender in sheet calender follow by emp names
i have only updates of leave off data apart from leave dates rest of all mark as P present
f
IN sheet rawdata A column emp name F column leave dates, H column what types of leave
for e.g if Paki Atkins took leave on 17-Oct-22 as AWA mention in calender table according to name row under date 17-Oct-22 mark as AWA
i updated for oct for your reference .please help on this
find the attachment
Re: Need VBA help to copy attendance status into calender table
for my understand i did it, just make condition formatting color for sat and sun, remove weekend and P only update AWA,BWA ,PH and Absent help me with your knowledge to think different way
Re: Need VBA help to copy attendance status into calender table
Hello. I attached a way:
PHP Code:
Sub Macro_25() Dim a, Q&, i&, b, c1, r1, mr, mc Application.ScreenUpdating = False a = Range("Rawdata!a1").CurrentRegion.Value: Q = UBound(a) With Range("Calender!a2").CurrentRegion c1 = .Columns(1): r1 = .Rows(1).Value2 ReDim b(1 To .Rows.Count - 2, .Columns.Count - 3) End With
For i = 2 To Q mr = Application.Match(a(i, 1), c1, 0): mc = Application.Match(CLng(a(i, 6)), r1, 0) If Not IsError(mr) And Not IsError(mc) Then b(mr - 2, mc - 3) = a(i, 8) Next
With Range("Calender!a2").CurrentRegion With .Offset(2, 3).Resize(.Rows.Count - 2, .Columns.Count - 3) .Clear: .Value = b: .Font.Size = 8 .SpecialCells(xlCellTypeConstants, 3).Interior.Color = vbGreen .SpecialCells(xlCellTypeConstants, 3).Font.Bold = True: .Columns.AutoFit End With End With Application.ScreenUpdating = True: MsgBox "Ok." End Sub
Last edited by beyond Excel; 11-23-2022 at 11:43 AM.
Bookmarks