Combine 2 VBA codes in one

  • Good evening,


    I have 2 VBA codes that I use in one worksheet, these codes both refer to something that is transformed into cell A3.
    These two codes I want to combine into one code. I also want to change the code so that it works instantly, even when something changes in a cell that
    the formula refers to in cell A3. the contents of cell A3 changes by the date of today(). at a certain date, it uploads information from one sheet to another worksheet. but with the codes I have now, he does so only when I adjust cell A3, and then enter or click another cell.


    these are the codes;





    I hope someone caNn help me out.


    Adile

  • Re: Combine 2 VBA codes in one


    Sorry


  • Re: Combine 2 VBA codes in one


    Not sure why you would rename the worksheet every time the user moves to a different cell... and please explain "...even when something changes in a cell that the formula refers to in cell A3".


    Does A3 contain a formula?

  • Re: Combine 2 VBA codes in one


    It was a code i got on a forum and it worked but i used it for more sheets and the name only changes when i move to a different cell. That is one of the things that I wanted to change. I want the name to change instantly.


    A3 contains the formula

    Code
    ="Per" & BR2


    Br2 contains

    Code
    =INTEGER(($BS$1+3)/4)


    Br1 contains

    Code
    =WEEKNUMMER(Today();2)


    Today is Per5, when it changes on 18th of may to per6 I want the sheet to load up the data from workbook best1 in sheet per6.
    and the sametime change the name of the sheet I am working in from per5 to per6.


    i hope i explained it well.

  • Re: Combine 2 VBA codes in one


    The basic problem is the Change event is not fired when a cell changes as a result of a calculation - which probably explains the sledge hammer approach...


    Using a phone at the moment so cannot suggest an alternative but perhaps someone else will happen along

  • Re: Combine 2 VBA codes in one


    Give this a try - not sure I fully follow the logic though:


  • Re: Combine 2 VBA codes in one


    @S O


    Thanx for your help! I tried the code and in the beginning the code seems to work properly but after I change cell BS2 with adding +1 then I get an erros and after the error the code won't change even the name of the sheet.


    but with your code i'm almost there.


    Adile

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!