Brief overview. What I am trying to achieve is the following:
Our Members have a unique individual membership number that is imbedded into their RFID card.
I have a USB RFID reader for when they enter the space they can tap in, this will record the member number only, on our master-sheet called "MEMBER LOGIN"
Now what I would like to happen is:
When their individual number populates into a column A, then the following cells A, B, C, D in that row, will automatically copy into that individual members sheet.
Note the Sheet names are labelled as the company that the member works for.
There are however multiple members with the same company. So, the layout if this happens is: Member1 Columns B-G, Member2 I-N and Member3 P-U and so on within that sheet.
I have included a sample of the basic layout. Please note the Members name and company name has been removed to adhere to company policy.
Within the sample you'll also see a VLOOKUP formula to lookup the member name and company name associated to that RFID number as well as a little VBA script to automatically update the NOW date and time.
The above is the fundamental requirements.
Although as an added bonus, is it at all possible: to have it when their number is registered for a second time, (tap out when leaving) to have the tap time copy into their individual sheet into the "Time Out" column, if not I don't mind manually entering this in.
Once this excel system has been created will there be an easy way for team members like receptionists to edit the workbook in a matter of minutes when adding new sheets, companies and members?
In Summary what I need is when a unique individual member number populates in column A, the cells A, B, C, D (A=Membership number, B=MemberName, C=Company & D=MACRO Time & Date) in that row atomically copy into that members individual company sheet.
So then at the end of the day, we are able to clear the Master-sheet while keeping the copied data now stored within their sheet.
Please note, the members enter and exit multiple times thought the day. Sometimes up to five.
We also need to be able to in future as the members numbers change, when we add new companies and sheets have this process to the new members, numbers and sheets.
The reason for this is to record all the members entering the space and produce a individual table summary at the end of month for each member.
If you believe there is a better solution to my requirement above please advise, as long as is meets the basic layout, I'm open to suggestions.
About the recuiterMember since Sep 20, 2017 Henry Taylor
from Victoria, Australia