I am looking for a code to automatically insert a value from another sheet into a specific cell when a condition changes on another. here's what I'm trying to do. If Worksheets 01 range "B16" changes then insert value from Begin Year range "C21" to Worksheets 01 range "H28". I've included a modified copy of the sheet. Thanks
Last edited by AliGW; 01-18-2021 at 04:08 AM.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are new, I did it for you this time.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Your code
Try this
Please Login or Register to view this content.
OrPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by mohadin; 01-18-2021 at 05:31 AM.
Private Sub seems to work other than I am getting a Runtime Error 09 subscript is out of range and it is completely changing the vacation and sick time. I only need the vacation and sick time to link to 01 sheet. I forgot to mention there are a total of 26 timesheets as well as the begin year sheet attached to the workbook.
Last edited by ffbaker81; 01-18-2021 at 07:51 AM.
Seems there is missing sheet\sheetsPlease Login or Register to view this content.
Didn't think it would allow me to post the entire sheet. I can't figure out why it's throwing the vacation and sick time out of whack, for some reason it is adding 12 days every 1 versus 1 every month. I appreciate all of your help, coding is why I changed my major to business when I was in college way back when. Then I got a degree in social work and now I'm a Firefighter.
Last edited by ffbaker81; 01-18-2021 at 08:09 AM.
Same problem in here
The formulas
Formula:
Please Login or Register to view this content.
I appreciate all of your help and for teaching me some new tips and tricks on writing code.
I never got the vacation day portion to work so I removed it and everything else still works fine. I tried modifying the code all the ways I could think of which aren't many but H28 on sheet one shows up correct, but the value of H28 through out most of the other cells jumps up 12 days
Originally Posted by mohadin
My department has basically been recreating 73 employee time sheets every year and they had been changed so many times the formulas for the vacation and sick time weren't working so I've fixed that, have it set up where certain cells on the 73 workbooks are linked to this master sheet. So when we go in and change the date in sheet 01 cell B16 it will clear thecontents needed to create all new sheets for the next year. I've got everything working as we need it except having the previous years remaining vacation and sick to autopopulate to sheet 01 cells H28 and H29 when we change the date listed above without removing the links I've set up for sheets 1:26 so that the vacation and sick flow through the entire workbook.
Is this what you mean or?
Let me know, Plesae
Thank youPlease Login or Register to view this content.
Awesome, that worked, I just had to add a reference to Sheets("26") and it did exactly what I needed. For some reason it isn't clearing everything in sheet 10-26? I have one final question and everything should be perfect. Is there something that I can add to the formula or coding so that cells L28 and L29 in each Timesheet populate the accrual formula for Vacation and Sick in sheets showing an EOMonth date in row 16 columns B:O? Obviously I'm not very good at coding and I probably got way in over my head, but I'm learning, and you have taught me more in a few days than I learned in 2 semesters 20 years ago.
Last edited by ffbaker81; 01-19-2021 at 08:46 AM.
Well you can do this by excel
1- just Hold CTRL key and select all the sheets you need to insert the formula in
2-Then type the formula in the cells you want ( L28 )
And hold the CTRL then hit the ENTER key
Repeat step 2 for (L29)
OK, do I need to add anything so that it only activates the formula on EOMonth sheets? Also for some reason it isn't clearing sheets 10-26
Re: insert a value from another sheet into a specific cell when a condition changes on ano
activate the formula and hold CTRL then hit Enter
regarding sheets 10-26 replace with code
See you tomorrow!Please Login or Register to view this content.
I created a Save As copy of the workbook to build off of and inserted a link to Sheet 01 Cell B16 of the Master Sheet so that I can control all of the other workbooks when I create them. The link works but it doesn't clear all of the cells like it does on the Master.
What do I need to do to create a Macro out of this that removes the B16 reference and just uses a key stroke command to activate this?
Please Login or Register to view this content.
Last edited by AliGW; 01-21-2021 at 09:24 AM.
Please Login or Register to view this content.
You knew I'd be back. I appreciate it.
Please Login or Register to view this content.
It's not working, what am I doing wrong? Is there a way to force the link through all of the sheets I create after they open without me having to activate it manually? All I want to do is to be able to control that one aspect from the Master Copy so that I don't have to change each Workbook individually.
Can I just remove the B16 reference and create a Macro to run the rest of the code to clear cells and update the vacation and sick? I'm not sure what to modify on this to make it work.
Please Login or Register to view this content.
Last edited by ffbaker81; 01-20-2021 at 11:45 AM.
I got everything done, the Formulas, the link, and created the Macro to reset and update the workbooks, and then after I went and protected the cells it's giving me a Run Time Error and I can't figure out how to allow a Macro to run with Protected Cells. That is the last thing I need to do to get these things done .
Glad you could sort out the issues
See this might help
Please Login or Register to view this content.
Still giving me the debugging error, the Vacation and Sick accrual is running, but the ClearContents command in the cells that aren't protected isn't. I put the code in the ThisWorkbook tab of VBA. Do I need to modify the code for the unprotected cells or enter it in every worksheet in the workbook or what?
Is there a way to say have the Macro look for the text change in B16 considering the formula isn't changing so it's not activating the Macro.
How can I combine the unprotect/protect command with the Macro and have them span all 27 sheets?
I also wonder if adding the Activate code may get the Macro Started.
Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by ffbaker81; 01-21-2021 at 11:29 PM.
OrPlease Login or Register to view this content.
Please Login or Register to view this content.
Thanks everyone
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks