stopping auto updateing of cells

[SRussell]

I have a sheet to calculate points scoring for an incentive I'm running for my staff. (sample sheet has been attached)

I have coded a macro to copy certain cells when it comes to the end of a week so that we can start with fresh figures, but also keep a running total.

If you look at the sample sheet, column X contains the score for the current week, column Y is the previous weeks score and column Z is the running total.

When I run the macro the sum of X & Y is pasted into Z and columns D to U (which contain the point calculations) are cleared which obviously means the value in X is now blank.

This is also reducing the value in Z because the sum X+Y is auto updating.
How can I stop this from happening?

The code I'm currently using is below

Please Login or Register  to view this content.

Thanks for any help given.

[BaLLZaCH]

What I do in those cases is use the first part of the macro to dump X1 and Y1 into a new cell, then do all your VB calcualtions from the x2 y2 cells, not the original ones.


Its the most simple way I have thought of at least.

[davesexcel]

Hi,
Give this a try,

Please Login or Register  to view this content.

[Richard Buttrey]

Whoa!,

Whenever you see repetitive code like that you can be almost certain that it can be simplified considerably. It looks like it's been recorded with the macro recorder, which is great for starting off with, but in the majority of cases it should only be a starting point. Almost invariably it should be modified and edited.

Replace it with the much simpler code below.

Please Login or Register  to view this content.

HTH

[SRussell]

Whoa!,

Whenever you see repetitive code like that you can be almost certain that it can be simplified considerably. It looks like it's been recorded with the macro recorder, ....

HTH

Hey Richard, you guessed right.

My understanding of vba is soooo basic, but you're right the macro recorder does the job until I learn more about coding.

Thanks for the code.

[SRussell]

Hi Dave,

unfortunately your code isn't working.
It copies X accross into Y but then Z reverts back to the new value in Y and the code

Please Login or Register  to view this content.

is pasted into X

Unfortunately I don't know what you're code is doing so I have no idea what the problem is.

Are you able to help further?

Hi,
Give this a try,

Please Login or Register  to view this content.

[davesexcel]

Hi Dave,

unfortunately your code isn't working.
It copies X accross into Y but then Z reverts back to the new value in Y and the code

Please Login or Register  to view this content.

is pasted into X

Unfortunately I don't know what you're code is doing so I have no idea what the problem is.

Are you able to help further?

Did you look at the formula in column x after you ran the macro??
you can't run the macro again until you have data in the appropriate cells, or you will just be transferring zero values to the next column.