+ Reply to Thread
Results 1 to 6 of 6

Need VBA help to copy attendance status into calender table

  1. #1
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    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

    thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    Re: Need VBA help to copy attendance status into calender table

    Any experts help on this?

  3. #3
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Need VBA help to copy attendance status into calender table

    Hi.
    I understand what you want to do and it's simple.

    However, let me tell you that I don't find sense in the letters "P" or "Weekend": they dirty the sheet and the visual!

    Is it really necessary to write these last two?

    And as for the ABSENTS, AWA, BWA and PH: Should they -yes or yes- stand out with a color?...

  4. #4
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    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

  5. #5
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Need VBA help to copy attendance status into calender table

    Hello. I attached a way:

    PHP Code: 
    Sub Macro_25()
    Dim aQ&, i&, bc1r1mrmc
    Application
    .ScreenUpdating False
    Range("Rawdata!a1").CurrentRegion.ValueUBound(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 2 To Q
      mr 
    Application.Match(a(i1), c10): mc Application.Match(CLng(a(i6)), r10)
      If 
    Not IsError(mr) And Not IsError(mcThen b(mr 2mc 3) = a(i8)
    Next

    With Range
    ("Calender!a2").CurrentRegion
      With 
    .Offset(23).Resize(.Rows.Count 2, .Columns.Count 3)
        .
    Clear: .Value b: .Font.Size 8
        
    .SpecialCells(xlCellTypeConstants3).Interior.Color vbGreen
        
    .SpecialCells(xlCellTypeConstants3).Font.Bold True: .Columns.AutoFit
      End With
    End With
    Application
    .ScreenUpdating TrueMsgBox "Ok."
    End Sub 
    Attached Files Attached Files
    Last edited by beyond Excel; 11-23-2022 at 11:43 AM.

  6. #6
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    Re: Need VBA help to copy attendance status into calender table

    Thanks alot boss, Saved my time and energy!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-09-2017, 05:36 AM
  2. Replies: 3
    Last Post: 09-11-2014, 04:32 PM
  3. Replies: 8
    Last Post: 01-28-2014, 11:02 AM
  4. Replies: 5
    Last Post: 03-31-2013, 09:24 AM
  5. Trying to take this table and format it into a calender
    By Zenverak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2012, 01:42 PM
  6. Replies: 4
    Last Post: 04-08-2012, 09:43 PM
  7. Calender table lookup query
    By Graydoggy in forum Excel General
    Replies: 6
    Last Post: 07-09-2009, 09:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1