Updating counters on multiple sheets

[Jack-P-Winner]

I have these 4 digit counters on multiple sheets that I have to update all the time. I have to copy and paste a new counter which is very time consuming. My digits always go up and start over again with 1 after the number 9. (1-2-3-4-5-6-7-8-9-1-2-3-4-5-6-7-8-9-1)

I made a video on how they work but basically I wasn't to be able to press one the letters (green U, Red U, Green D, Red D) and have that number in that column go up 1 digit

example: 1111 is in columns ABCD and A=green U, B= Red U, C=Green D and D=red D. I want the Green D to go up 1 digit so the new number would be 1121, If I wanted to red U to go up the new number would be 1211. I need the conditional formatting to be for all Odd numbers having bright yellow shade with red font and all even numbers having gray shad with green font

Thank you so much for your help in this as it will save me a ton of time plus mistakes. Here is the spreadsheet and I will upload the video if you need me to. This is a shorter version as there are 20 sheets in each book with counters but the columns' and cells are the same. I will put a dropbox link too for the main book

Again, thanks

 

[Jack-P-Winner]

http://www.mediafire.com/view/96dqmtpp0797bbd/MASTER_Updates1xlsx.xlsx

 

[Jack-P-Winner]

Here is the video

http://www.mediafire.com/download/n25xp9jn8c64g8e/Count_Up_each_row.camrec

 

[Giloy]

Hi Jack. Try and check if this works. Let me know.

 

[Jack-P-Winner]

Off to a good start but I need all 4 digits to go up a row but only the 1 to count up. Example, 1111 would go to 2111 or 1211 or 1121 or 1112. I would like to be able to go to the spread sheet tabs and click on the 1st row column CDEF JKLM QRST etc... and when I click on 1 of the number/letters (1E 10) it will change that specific digit counting up and move the other 3 up with it all at once. Make sense? Great start

 

[Giloy]

Hi again Jack,

Here is updated file. Do examine the code. Revisions to be made are the following:
1) define all columns possible needed in your game i.e. CDEF, JKLM, etc (this can easily be modified in the code)
2) define starting row -> i'm guessing row 71? (current code does not handle this yet.)
3) what is default value upon start? i.e 1111 (current code does not handle this yet.)

 

[Jack-P-Winner]

Wow!!! This looks like its perfect! (-: I will play around with this and also look at the code to see if I can perhaps add an "AND" code. What I mean is some sheets all the them may need the same adjustment and lets say I have 20 separate counters in one sheet (tab) and instead of going to each and every one, press a cell and it updates all of them at once. That would be so cool LOL

 

[Jack-P-Winner]

What I noticed is I can not add or do anything to row 1 without the code popping up. I tried to add a row and call it "Update All" When I tried to do this the code keeps coming up. I need to be able to add additional things like 1E, 2E, 3E, 4E, 5E. 6E, 1O, 2O.....) hmmm do not know why its doing this

I am trying to understand code so sorry for not knowing how to change things "yet"

 

[Jack-P-Winner]

Question 3: default values

There is not a default value. All counters will have a different starting 4 digit number. Although some will track identical for a few to a dozen a times but then go there own direction

 

[Giloy]

Interesting game you have there. Currently, code does not handle flexibility of row1 (i.e. range CDEF, JKLM, etc were pre defined as the buttons.) So if you insert a column, this would probably cause an error. My questions are these: (can't view your video so I really can't get the whole picture)
1) Is this a frequent thing in the game that you need to insert columns?
2) What is the intended action for "Update all" button?
3) What do you mean by this? "I need to be able to add additional things like 1E, 2E, 3E, 4E, 5E. 6E, 1O, 2O.....)"
I'm thinking odd or even. Do they have specific functions?

 

[Jack-P-Winner]

If you go to the media file you can see what I'm talking about with the different tabs. For example tab 1 has E-O1 which is #1 Even or Odd. Tab 2-3-4 same #2-3-4 Even or Odd next section EO1 means Up or down (EO1-2-3-4). Next set EEOO means up or down again. I've added the other tabs and you can see the pic that I have many tabs the Macro will be working in plus have several books containing 22 sheets that will be all done the same exact way (all columns and rows have the same info so Macro will work)

Basically what you provided is on target for the individual tab sheets ... PERFECT I've added the extra columns of contents to your code THANK YOU!

What I'm trying to do next is let's say I have 10 separate counters (the 4 digits) and they all have the 3rd digit going up, I would like to be able to press a (1cell) button and all 10 go up just like the 1 goes up now Hope that makes sense I'll look over the code and see if I can make a change. Like I said, I, learning Macros. Getting better each day thanks to folks like you and Narayan. (-;

 

[Giloy]

Glad to help. =) Just a clarification?
1) Aside from the indivdual counters (4 digit), you need a master counter (4 digit) which updates all counters?
2) If yes, where do you want the master counter to be placed in the tab/sheet?

 

[Jack-P-Winner]

I added some additional features to the Macro. I want to be able to click on B1 and it add the next draw date to cell B15,I15,P15,AD15,AK15, ....) the date will be counting up and I am starting at row 3000 on my MASTER sheets but I can change the range and add the additional columns that the date is in. I have something wrong

Thanks

 

[Jack-P-Winner]

Glad to help. =) Just a clarification?
1) Aside from the individual counters (4 digit), you need a master counter (4 digit) which updates all counters?
2) If yes, where do you want the master counter to be placed in the tab/sheet?

Look at my current upload. B1 has Next Draw Date button and Update all is C1. The individual ones will be DEFG, KLMN and so on but I can add those. I just need help getting started

 

[Jack-P-Winner]

scratch the draw date option. I am just going to drag them up. That's a lot easier lol

 

[Jack-P-Winner]

I know anything is possible and tell me if I should start a new post. I would like to when I place a U or E in columns H,O,V,AC,AJ,AQ,AX,BE,BL,BS,BZ,CG,CN,CU,DB,DI,DP,DW,ED,EK,ER,EY,FF,FM,FT,GA,GH,GO,GV,HC,HJ,HQ,HX,IE,IL,IS the letters are bold dark green and if I place a D or O its a bold red letter. How can I do this? Again, I can do them once its started

Thank you!!!!!

 

[Giloy]

For the color change, you may use conditional formatting just like what you did to the even and odd numbers.
Here is another file. it adds a letter to the right based on letter on the top.

 

[Jack-P-Winner]

Hey too cool and thanks. How can I get the CF to follow up when it counts up? When the new number generates the conditional formatting does not go up with it now and leaves no color. The Red Even I can probably change in the CF. Red is O and D, Green is E and U (-:

 

[Jack-P-Winner]

Guess I am wrong LOL, I do not know how you are turning the letters red off to the side of the counters. LOL so, Red O and D, Green E and U thanks Giloy

Is the Reset all possible?

 

[Giloy]

Please do edit/ manage rules of conditional formatting to include the desired ranges. I have done a sample for columns DEFGH and KLMNO. Anyway, have I answered your query regarding this post "updating counters on multiple sheets"? If yes, maybe you can start another thread for your other queries. Thanks.

 

[Jack-P-Winner]

PERFECTLY!!!!!!!!! You are the bomb Giloy!!!!! I can edit the CF

Should I do a new post to do the update all?

 

[Giloy]

Appreciate the complement. =) Yes please do another post.

 

[Jack-P-Winner]

Giloy,

When I put the Macro in my other workbook and change the range to 3000, nothing happens. Even with this down load the first page(not the test page) does not work. What am I doing wrong?

I open a new module and copy your code and paste it and then save. When I re open it is gone?

 

[Giloy]

In the vba screen, go to Project - VBA Project. Under "Microsoft Excel Objects", double click the sheet you are working on. Then paste the code there. How many sheets do you have total? Also you may have to edit the value of "BeginningRow" inside the code.

 

[Jack-P-Winner]

Thanks Giloy for responding back. I have 54 sheets in each book that are all tabulated with a form of E-O, EO, EEOO AND BBE-O, BBEO, BBEE. If you need specifics I can tell you or open the first book I posted in the first message