Recording on to another worksheet

**hboss_1** · 05-07-2021, 08:28 AM

Hi,

Please take a look at the below screenshot (I have a table for each week of the year) of a roster:

excelrosterhelp.png

Where AL (Annual Leave is inputted) I need to record (on a separate sheet) the date of the AL. AL is inputted below the scheduled worktime normally but we currently have no system to record these dates. I need something that will show (for each person) the dates where 'AL is inputted'.

So for example David has AL submitted for thursday 22/02, I need this date recorded on another sheet.

Any help will be much appreciated.

Thank you,

H

**Bernie Deitrick** · 05-07-2021, 11:50 AM

1) Copy this code.
2) Open the VBE (press Alt-F11)
3) Select your workbook in the project explorer
4) Double-click the ThisWorkbook object
5) Paste the code into the window that appears.
6) Save the file as a macro-enabled .xlsm file.

Change "Name of the sheet where the data is stored" to the actual name of your separate summary sheet - I have assumed that you want the name in column A, and the date in column B

Please Login or Register  to view this content.

**hboss_1** · 05-10-2021, 06:47 AM

Hi,

Thank you for your response!

I have put the code in and inputted a separate sheet name as you said but nothing is happening? Do I need to play the macro ? I'm not sure what else I have to do?

Thank you,

H

**hboss_1** · 05-10-2021, 06:48 AM

Hi,

Thank you for your response!

I have put the code in and inputted a separate sheet name as you said but nothing is happening? Do I need to play the macro ? I'm not sure what else I have to do??

Thank you,

H

**Bernie Deitrick** · 05-10-2021, 09:06 AM

If you followed my instructions correctly, and have enabled macros, whenever you type AL into a cell on one of your sheets, the name and date should be recorded on the sheet you specified.

**hboss_1** · 05-10-2021, 09:19 AM

It is giving a 'Compile Error: Block if without End if' error? Any Ideas?

**hboss_1** · 05-10-2021, 09:29 AM

Hi,

The file is here (non macro version): Excel Roster Help.xlsx

Please could you take a look?

Thank you,

H

**hboss_1** · 05-10-2021, 09:41 AM

Hi Bernie,

I got it to work! ANNND it is brilliant!

Thank you so much!

Is there any way that the recordings are removed if the annual leave is removed, say down the line a leave day is cancelled and change it on the roster?

Thank you,

H

**Bernie Deitrick** · 05-10-2021, 10:37 AM

Sure, but that would require a temporary special entry.

Let's say that you had a cell with AL, and you want to cancel it. Use this code and enter - oh, let's say CAL for Cancel Annual Leave - and then the code will remove the corresponding entry from your record sheet and clear out the CAL from the cell where you just entered it.

Give it a try and let me know if it works.

Oh, and sorry about the missing End If - don't know what happened to it

Please Login or Register  to view this content.

**hboss_1** · 05-10-2021, 11:01 AM

Hi Bernie,

This is absolutely Brilliant, you are a genius!! It works Perfectly

Thank you,

H

**hboss_1** · 05-10-2021, 11:31 AM

Hi Bernie,

It works great, the only one thing is if I have several tables on the worksheet (for each week), when I put AL into one of the others ones (not the top one) it will pick up the date from only the top table, you will see what I mean by tables on one sheet here: Attachment 731896

I want the date at the top of each table, as you've done, but just not for the top table.

Sorry for being such a pest!

H

**Bernie Deitrick** · 05-10-2021, 01:45 PM

Try it like this - I have assumed that the date is in the second row of each table, each table has names in the column B of the sheet, and that there is at least one entirely blank row between tables. (Your attachment didn't work.... so if your layout is different, let me know.)

Please Login or Register  to view this content.

**hboss_1** · 05-11-2021, 04:01 AM

Hi Bernie,

This seems to work brilliantly, but an issue I'm having is that if put AL into a table further down it does get recorded on the separate sheet but it is being recorded 3/4/5 times (duplicates), once again sorry for being such a pest but do you know why this is happening?

Thank you again Bernie,

H

**hboss_1** · 05-11-2021, 04:46 AM

Hey Bernie,

I think (fingers crossed) it's working okay now. You have been a lifesaver! Whilst I have your expertise here, is there any way to almost click buttons (one for each month) to show me only that months tables. If I am clutching at straws please ignore this but I am just wondering about the endless possibilities after your brilliant work here!

Thank you,

H

**Bernie Deitrick** · 05-11-2021, 07:39 AM

Did you figure out why you were getting duplicates? I think the code wasn't the source, since it worked fine for me.

As far as using buttons - that easy, but the code would be highly dependent on your workbook structure. Read the instructions in the banner at the top of every page HOW TO ATTACH YOUR SAMPLE WORKBOOK:

**hboss_1** · 05-11-2021, 08:56 AM

Hi Bernie,

Can you open the attachment now? (I have changed the name of the second tab just for indication purposes, it does not need the 'will be hidden part' and that part is removed for the VBA code)

Thank you,

H

**Bernie Deitrick** · 05-11-2021, 10:07 AM

Try this version - I added tables that include May, so you can answer 4 or 5 when asked for the month number.

**hboss_1** · 05-11-2021, 10:21 AM

Once Again, this is brilliant. It is exactly what I wanted, I'm going to start putting real data in, thanks to you of course. Once that button is pressed how would one go back to the default view of all the tables?

Thank you,

H

**Bernie Deitrick** · 05-11-2021, 10:54 AM

Use this code to replace the existing code - in Module1:

Please Login or Register  to view this content.

**hboss_1** · 05-11-2021, 11:06 AM

Perfect! One Final, Final, Final Thing (I promise)! If you see the 3rd tab, it will provide a summary table of all dates that each employee will have leave for. When I clear Annual Leave (using the brilliant CAL method), the summary tabs formulas change to #REF, is there any way to prevent this?

**Bernie Deitrick** · 05-11-2021, 11:54 AM

I don't get errors, but the dates should be sorted properly for display on your summary sheet. (I also changed that formula)

**hboss_1** · 05-12-2021, 07:47 AM

Hi Bernie,

Thank you so much for all your work, I think it is working exactly as I wished. If I could buy you a drink I would!

Thank you,

H

**Bernie Deitrick** · 05-12-2021, 09:13 AM

The next time I'm in the UK....

**hboss_1** · 05-19-2021, 04:34 AM

Hey Bernie,

The workbook is very much how I want it now. There are 2 things I thought I'd ask you if you have a chance, firstly is there any way to clear the month selection when exiting/opening the excel document, say if someone filters on March and closes the workbook. Rather than the next person open it and panic (a lot do!) is there a way where this filter/selection is cleared?

Secondly, In my summary sheet (with the dates of annual leave summarised, with IFERROR INDEX Formulae) I need a way to count the number of days. This would ordinarly be straight forward but there are formulas in the cells, so I'm not sure how to do this. Ideally I'd want (to the left of the name), the total dates taken as leave.

I know I'm mithering you again but seeing as though you've done so much for me already I thought I'd ask.

Thank you,

H

**Bernie Deitrick** · 05-19-2021, 10:00 AM

Sure - here you go....

**hboss_1** · 05-19-2021, 10:42 AM

Hi Bernie,

I have only just seen your response despite being on the forum!

As always your solution is simply brilliant, genuinely don't know what I would have done without your help!

I posted another thread (mainly because I don't want to keep mithering you) around combining VBA codes but I think its just better I ask you!

If I say needed another summary sheet for Complimentary Leave (so the same as for Annual Leave but instead Complimentary Leave), how would I go about the VBA code. I tried copying the previous VBA code (replacing the AL sheet with a CL sheet) and replacing 'Annual Leave' With 'Comp L' and then tried merging the two codes together but had no luck?

Is this scenario possible at all with this specific code?

Thank you as always Bernie,

H

**Bernie Deitrick** · 05-19-2021, 11:48 AM

Use Comp Leave and CCL to trigger the code.

**hboss_1** · 05-20-2021, 07:51 AM

Hi Bernie,

As always, this is is simply brilliant.

Thank you enormously, if you are ever in the UK please do let me know!

H

**hboss_1** · 05-20-2021, 09:48 AM

Hi Bernie,

If I want to add a column between the name column(B) and the first day of the week column (C)- to show the area that person is working in, how would I alter the VBA code to still reference AL and CL? I've tried changing it but I just end up messing it up.

Thank you,

H

**AliGW** · 05-20-2021, 09:53 AM

You might want to remove the solved tag from the thread ...

**hboss_1** · 05-20-2021, 10:10 AM

Hi,

Bernie has solved my issue but this question is related to changing the code slightly to compensate for an extra need (hence there is no need for a new thread)

**Bernie Deitrick** · 05-20-2021, 11:00 AM

I think this is what you want....

**hboss_1** · 05-20-2021, 11:28 AM

Literally EXACTLY what I needed, your skills are genuinely unparalleled!

Just a quick question, with regards to adding a new member of staff. Is there any existing way whereby if I add a new member of staff this gets added to all the below weeks as well? I noticed how all your tables had the business area in them, did you do this manually (copy-paste)? If there is a way whereby if new staff are added the change is reciprocated below, otherwise if not it is no issue I will just make the tables bigger and add spare boxes.

Thank you Bernie,

H

**Bernie Deitrick** · 05-20-2021, 11:49 AM

You could set up your file to include 2X rows for X new staff members in each area. Then fill the first table of names with "Not Used" - and then link the other tables to those entries. You could then hide the Not Used rows with code, and use a special value to unhide all the rows so that you can enter a new member, which will remain visible since the value will not be "Not Used" Sound like a way to do it?

**hboss_1** · 05-20-2021, 06:34 PM

Hi Bernie, that sounds great, all but the code for hiding the not used rows- what would that code entail?

**hboss_1** · 05-21-2021, 06:30 AM

Ignore this message (deleted)

**hboss_1** · 05-21-2021, 08:06 AM

Hi Bernie,

Ignore the previous reply, it was a conditional formatting issue, I had a load of extra rules that were slowing the whole thing!

I'm loving how it is now, I think I should scrap adding a new member of staff but instead what are the possibilities of having name buttons along the top, allowing a member of staff to click on their name and then filter on all their shifts (rather than every single week for everyone), the reason I ask (although it sounds complicated) is that wouldn't it work similar to the month selection you created? If I am stretching the realms of possibility then please let me know!!

Thanks,

H

**Bernie Deitrick** · 05-21-2021, 09:12 AM

Try this one - I created a list of names in column V - fill in your real values starting in V2 (always leave V1 intact).

**hboss_1** · 05-21-2021, 10:18 AM

Simply Brilliant, you are an excel GENIUS! This is going to help me so so much. Similar to the other clearing code is there a code that will remove the filter when opening/closing the workbook?

**Bernie Deitrick** · 05-21-2021, 10:26 AM

Stop! You're making me blush!

**hboss_1** · 05-21-2021, 11:29 AM

WONDERFUL, I cant thank you enough, the things you've done have been so inspiring and make me want to learn much, much more, I had never used these forums before but I think your help will definitely trigger me to use this more. Thank you again and sorry for the continuous questions, you are a real gentleman!

Thank you,

H

**Bernie Deitrick** · 05-21-2021, 11:59 AM

Great - Excel is a powerful tool for organizing information, and VBA extends its usefulness immensely. So keep learning, and asking questions, and you'll find many uses for your new skills.

Good luck!
B

**hboss_1** · 05-26-2021, 04:50 AM

Hi Bernie,

Using your VBA code, I've added around 10 different scenarios (AL, unpaid leave etc) and it is simply brilliant. I was wondering if there is anyway to add a part to the VBA code whereby instead of 'If Target.Value = "xxxxxx" " we have 'If Target.Value CONTAINS xxxx'?

This is for example if a member of staff is on a training course that day, I can put "TC" and then the name of the course, but the date of that course is then recorded. Of course currently I can only put "TC" and nothing else.

Just exploring possibilities!

Thank you,

H

**Bernie Deitrick** · 05-26-2021, 10:29 AM

The code for 'If Target.Value CONTAINS xxxx' is

Please Login or Register  to view this content.

or, if TC will always be the start of the string

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Then there is this that will find TC anywhere:

Please Login or Register  to view this content.

And a few more that really are bad practice

So take your pick - I prefer the version using Left

**hboss_1** · 05-26-2021, 10:57 AM

Hi Bernie,

I missed out a little part of the VBA code, I changed it and (as always) it works a charm!

Thank you Bernie!

One question, my previous system would rely on me putting comments on certain cells indicating that person did a certain amount of overtime. This is of course is an awful method as it is hard to spot comments when it comes to filling in status returns. Is there anyway whereby I can put 'Overtime' into a cell followed by text (which may be times or rates) and not only am I taking the date of this overtime (as the VBA code currently does) but it also takes the text after the 'Overtime' and records it.

I hope you understand what I'm getting at, alternatively if you think there is a better way of recording this please let me know!

Thank you,

Your friend,

H

**Bernie Deitrick** · 05-26-2021, 11:18 AM

If you enter, for example,

OT 4:35

Then this bit of code will extract the time part, and show a message box with 4:35:00 AM (but don't use the messagebox in your final code)

This assumes that Option Base has not been set to 1 (I'm pretty sure it is not)

Please Login or Register  to view this content.

**hboss_1** · 05-26-2021, 11:44 AM

Hi Bernie,

So I put this code into a new module? Or the existing VBA code?

So say with your example if (when it works) I put in OT and say 4:00 (meaning 4 hours overtime), this will get recorded in a separate sheet or? Sorry, just a little confused!

Thank you,

H

**Bernie Deitrick** · 05-26-2021, 12:13 PM

So - Enter Overtime like so, OT Time Note, like

OT 2:15 Doing what he does

And it will appear in the OT database.

To remove OT, use COT

If there is no time associated with the OT, use

OT 0 Note (that's a zero)

**hboss_1** · 05-27-2021, 08:49 AM

Hi Bernie,

Is there anything you can't do?

Works absolutely brilliantly!

The only issue (which is itself a minor issue) I'm having is if I put OT and then the time in and it submits, if I click on the cell again (without changing anything) and click away it will duplicate the entry? So in essence my question is, is there anyway of preventing such duplicate values?

Thank you,

H

**Bernie Deitrick** · 05-27-2021, 10:56 AM

This should fix that.

**hboss_1** · 05-27-2021, 12:15 PM

And indeed it does! One slight issue I've got with it, if I put OT in, with some text and hours it submits great, if I click and click away the new code does brilliantly and removes any duplicates, but if I then click and click away again a duplicate forms with a change in format of the hours cell (showing AM/PM), take a look in the attachment.

I think that is pretty much it! One final thing (just because I have never utilised VBA to this extent), ideally I want the scenario where employees can only view the main page roster and use the month and name filters and that is it (I don't want them to see other peoples leave or overtime for example in the other tabs). In a normal scenario of course I would just hide and protect sheets, which would work- but in this case when I do protect the first page as view only (and require a password to edit or unhide the other tabs) the month and name filters/buttons do not work as a result. Only those that then know the password can edit/view the other tabs.

That is the final thing I will ask of you Bernie, I know I've asked so much but the more you've shown me the more I've come up with possibilities in my head, some things I could never have imagined and as a result I've learnt so much.

Thank you,

H

**Bernie Deitrick** · 05-27-2021, 02:20 PM

The best thing would be exporting sheets to separate workbooks and giving the workbooks to the individuals. Use this version, and place the second attached file "Report Template.xlsm" into the same folder.

**hboss_1** · 05-28-2021, 04:17 AM

Hi,

This method is ingenious, I will test it out fully and assess the effectiveness!

With regards to the duplicate issue, is there any solution? Can the format of the 'hours' taken from OT cells be set in the VBA code?

The issue from above (I edited my response so you probably didn't see it!)

One slight issue I've got with it, if I put OT in, with some text and hours it submits great, if I click and click away the new code does brilliantly and removes any duplicates, but if I then click and click away again a duplicate forms with a change in format of the hours cell (showing AM/PM), take a look in the attachment.

Thank you Bernie,

H

**Bernie Deitrick** · 05-28-2021, 09:40 AM

Honestly - at this point, I would just advise you "Don't do that" Are you really going to click and click and click on one cell? Enter the OT and leave it alone. If you need to change it, cancel it first, then re-enter a new value, and then.... leave it alone. If you're not the only one entering the values, then you may have to clean up after someone else

**hboss_1** · 06-02-2021, 09:50 AM

Hi Bernie,

Hope you are okay!

I have altered the month selection code slightly so when the selected it will unlock the sheet, filter the month, then relock the sheet. The only issue I am having is with the name selection, I want the name selection to do the same thing; i.e. when a name is selected, unlock the sheet, filter, then relock the sheet. This is to prevent any edits by employees.

Any ideas?

Thank you,

H

**Bernie Deitrick** · 06-02-2021, 01:00 PM

Unlock cell H3 so that it can be modified when the sheet is protected, and change the code in the Sheet1 codemodule to

Please Login or Register  to view this content.

**hboss_1** · 06-21-2021, 09:55 AM

Hi Bernie,

I'd just like you to know that the roster system is up and running, with no real issues (so far!). I added a few different things to them ( you were my inspiration) and so I would just like to say Thank you for all your help. Your brilliance has taught me a lot and I aspire to one day reach your level with excel!

Thank you,

Your friend in the UK,

H

**Bernie Deitrick** · 06-21-2021, 11:00 AM

H,

That's great - nice to hear that everything is working. If you ever have any other issues, I or some of the talented folks here will be glad to help out.

Be well - watch out for Delta!

Bernie

Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Re: Recording on to another worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Recording macro to copy the data from one worksheet to another worksheet

[SOLVED] Recording a high price on a worksheet

[SOLVED] Recording Changes Made to Worksheet

Bookmarks

Bookmarks

Posting Permissions