VBA macros - copy & paste cell values for statistics purpose

[akarev]

Hi everyone,

I need your help to solve a really basic problem which is driving me nuts. Basically, I have created a new Sheet in existing Excel file with two very simple tables as shown on the image beneath:

Image_1.jpg

The first table value (i.e. B1:D1) are updated o:n a regular basis whenever a new record is added in Sheet "Call_centr". Now, I would like to write a simple script, which would do the following:

a) it should copy the values from cells B1:D1 and add them to values in cells B6:D6.
b) Once the first part is done, it should set all the values in cells B1:D1 to "0".

I will assign this macros to the button on the main sheet, so whenever someone will press it, it should do the functions described above.

Thank you very much in advance

[Andrew-R]

Something like this should do the job:

Please Login or Register  to view this content.

[akarev]

Hi Andrew,hank you very much for your reply, - now I know how to copy and paste using VBA.

Andrew, I have one more thing which I wanted to ask you. Basically, in your script, the system copies data from cells B1:D1 and pastes it to the cells B6:D6, but is it possible to make it so that if there would already be some data in cells B6:D6, the system would simply add up the numbers?

In other words, let's say there are the following numbers in B6:D6 ; 3, 5, 7 accordingly and in the cells B1:D1 there are let's say the following numbers, 10, 4, 7. Now, when I run the script I wish it to copy the numbers from cells B1:D1 and add these numbers to the numbers in cell B6:D6. Hence after running the script values in cell B6:D6 should be 13, 9, 14.

Thank you very much in advance


P.S Here's my great script (please do not judge it too harsh. It does not work as well as it should though.

Please Login or Register  to view this content.

[Andrew-R]

Basically, in your script, the system copies data from cells B1:D1 and pastes it to the cells B6:D6, but is it possible to make it so that if there would already be some data in cells B6:D6, the system would simply add up the numbers?

That's exactly what my code does - it's not just copying and pasting the data, it's using Paste Special, with the operation set to add, so it will add the data it's copying to the data that's already in the paste range.

Is that not what it's doing for you?

[akarev]

Andrew,

My bad. It works! I just mistyped one thing in the script. Now it all works like a charm.

Andrew, one last thing: why whenever I try to copy one cell value (from let's say Sheet1) and paste this value to another cell in let's say Sheet6, it automatically deletes all the other data in Sheet6?

Here's the code:

Please Login or Register  to view this content.

I have attached the actual document (Excel file). When you open it, go to the last Worksheet (names "Statistics"). On the right side, there's a table with the name of months. Now, open the first sheet (names "Call_centr") and press the Command button on the left side (with green text inside). The UserForm will appear, just type in anything in there and press the green button at the bottom. This will add a new record to the "Call_centr" worksheet and will automatically add +1 to the cell B2 in "Statistics" worksheet (Sheet(6)). However, the problem is that it deletes all the data in the table with the names of the months. I don't know why this is happening. If you would be able to help me with this issue you will be my hero :D

Intergaz_Windows_working_version.xlsm

Any help will be highly appreciated

Thank you very much in advance.

[Andrew-R]

It does it because you tell it to - that's often the way with programs

The code above calls the macro CopyPaste, which contains the code:

Please Login or Register  to view this content.

What that code is doing is looping through every sheet in the workbook and, unless the sheet name is Call_centr it is clearing (i.e. deleting) the contents of the Range A2:AD65536.

I'm guessing you don't want to do that.

[akarev]

Andrew,


Aaaaaaah, I see. That makes sense. So, in order to avoid this, I need to add an extra code of line to this macros such as:

Please Login or Register  to view this content.

I guess it is not that simple, but anyway, I got what you are saying and I will try to work out a way for it to ignore the Sheet "Statistics"

Thank you very much for your help Andrew. Also, if you'll have some free time, would it be possible for you to send me a solution (How to make it so that the code would ignore the Sheet "Statistics") in private messages.

I am closing this thread now because it started to go into a slightly different direction

P.S I gave you a REP Thank you Andrew!

Sincerely,

Alex

[akarev]

Andrew,

I got it. This is how I changed the code and it seems to work:

Please Login or Register  to view this content.

Bingo!

[Andrew-R]

Thanks for the rep, happy to help you out.

The code you're looking for really has to be posted here, as per the forum rules, but it's quite simple:

Please Login or Register  to view this content.

Hope that gets you sorted.