Macro To Advance Month

Hi,

Can anyone advise me of the macro code I need to advance my ‘Current Month’ formulas.

What I have on the ‘Summary’ tab is the months of the year i.e. starting from ‘Oct’ to ‘Sep’ with formulas only in the ‘Average %’ and ‘Total’ cells in rows 7 & 8 respectively.

What I require is a macro to :-

(a) to copy the two formulas for the ‘Current Month’ i.e. in formulas in the cells in rows 7 & 8 into the next column and then move the text ‘Current Month’ (black on purple) to the next column

(b) the previous formulas should then be converted from formulas to values i.e. override the formulas with the existing values

(c) when the macro is run when the ‘Current Month’ is ‘Sep’ the formulas should then be copied to ‘Oct’ and the data for ‘Sep’ treated in (b) above

(d) Finally, when the macro is run when the ‘Current Month’ is ‘Sep’ as in (c) above all the data for ‘Nov’ to ‘Sep’ should be deleted i.e. leaving blank cells

Please see attached example ‘Annual.xlsx’ file

Any assistance in this matter would be greatly appreciated

Man thanks in advance

Regards

Rob

N.B. Excel versions being used 2010 & 2013

Hi Robert,

Try this and let me know:

Please Login or Register  to view this content.

xladept,

man thanks for your reply it is very much appreciated, unfortunately nothing seems to happen !! I've checked to see if macros are enabled but still nothing happens ! Any Ideas ?

Rob

Well since the Current Month is filled in nothing should happen - I tested it by changeing the month -

Please Login or Register  to view this content.

xladept,

thanks for the revised code which worked once i.e. it advanced the month from 'Nov' to 'Dec', however, when I ran it again nothing happened ! What I require is for the macro to run every time you run it, so I guess the macro must check every time which cell has the text 'Current Month' in order to advance the month.

Any ideas ?

Regards

Rob

Hi Robert,

You can just change the red date - or I could rewrite the program to always advance the month

xladept,,

is it possible you could amend your code to always advance the month...if you could I would be very grateful...


Many thanks...

Rob

Any ideas or suggestions would be very much appreciated

Hi Rob,

Try:

Please Login or Register  to view this content.

*I must apologize for the delay in response. In my defense, but no real excuse, is the fact that this forum’s e-mail notification feature has been inoperative for an intolerable amount of time. I knew that but only now have I decided to check the status of the threads to which I’ve contributed. Sorry

xladept,

many thanks again for reply, we're almost there. I updated your code in my workbook and it does advance the month as requested, however, when the 'Current Month' is September and you run the macro again it does copy the formulas to 'Oct' and delete all the data for the months 'Nov' to 'Aug' (as required) but unfortunately it does not delete the data for 'Sep'.

Could I trouble again to take another look?


Many thanks

Regards

Rob

Hi Rob,

Just comment out that line for Sep:

Please Login or Register  to view this content.

xladept,

BINGO !! Once again you have nailed it !!

Excellent, many thanks for all your assistance it is 'Very' much appreciated

Regards


Rob

You're welcome and thanks for the rep!