Revising existing macro to be more dynamic- count the occurrences of usernames based on a date in multiple files and update the tracker

  • Hi!

    I have around 20 odd files created everyday and in each file I just have to read Column BI having usernames and column BJ having dates and update the count of occurrences of username on that date.So in the below code I have usernames hard coded which is a drawback if usernames are added/removed I need to change the code every time.Also this report must on a daily basis attached is the tracker file.

  • CapG

    Changed the title of the thread from “Revising existing macro to be more dynamic- count the occurrences of usernames in multiple files and update the tracker” to “Revising existing macro to be more dynamic- count the occurrences of usernames based on a date in multiple files and update the tracker”.
  • Okay.Let me make you understand.There are around 20 odd workbooks (attached is a sample workbook ie, one among those 20 files please see column BI and BJ) gets generated daily in which users creates ID on a daily basis and that is tracked by columns BI and BJ . Column BI has username and BJ has dates on which ID was created.So in the tracker file (attached) , there are dates/months when selected a particular date that should loop through each workbooks to get a consolidated status of each user and their count of ID's created everyday by running this tracker file.


    Hope I made it clear.

  • Possibly...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Change this line

    Code
    1. v = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("BI").Offset(5))

    to this

    Code
    1. v = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("BI")).Offset(5)

    If I've been helpful, let me know. If I haven't, let me know that too.

  • I made a small change to eliminate the counting of blank cells...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • This should work now...

    If I've been helpful, let me know. If I haven't, let me know that too.