Macros? based on colour.


Microsoft Office Proffessional Plus 2019

First of all I have been enjoying the forum and this will be my first post and sorry to say it's asking for help. I only have a basic understanding of excel but find it really helpful in running a 'Last Man Standing' competition for a social club I am involved with.

You may be aware of the premise but  fundamentally it starts off with aproximately 100 to 150 members predic ting the winning team (in the EPL) every week. If your pick draws or loses you get a yellow card. 2 wrong picks and you turn red meaning you are out. Obviously after week 2 there are a few 'reds' so week 3 has a number who need to be deleted.

I was wondering if I were to copy Week 1 over say 12 to 13 consecutive worksheets is the a way to link worksheets so that the following week(s) after week 2 automatically delete all the reds in week 3 and then after that week the same happens when more reds appear in week 3 (etc).

Thanks in advance to anyone coming to my aid.



Selected Answer

I made some changes to your workbook. In particular, I removed the blank second line and I moved some of the hidden blank columns to a location before the club name column. I also added one such blank, hidden column because I needed an identical column count for each set of columns and one seemed to be missing.

Please look for this enumeration at the top of the code sheet called TXL_4326.

    NspAnchorClm = 2            ' 2 = column B
    NspTblClmCount = 5          ' number of Table Columns
    NspCapsRow = 2              ' captions row
    NspFirstDataRow             ' next after captions row

It's the value for NspTblClmCount which must be kept in synch with the reality on the sheet. Currently there are 5 columns per set, 2 of them hidden, which doesn't matter. If you delete them, delete them in all sets, or add more, but adjust the enumeration to show the correct number of columns per set.

You can add columns outside the sets on the left of column B. That column is identified as NspAnchorClm and now has a value of 2, meaning your first column set starts in column B. All other addresses are calculated from there using NspTblClmCount.

Most of the code is in the standard code module TXL_4326. Dragging it between VBA projects in the VB Editor's Project Explorer pane will create copies, not move the modules. That's convenient for transferring code between projects, such as from mine to yours. Unfortunately, this easy method isn't available for code in worksheet code modules. The code in the code module marked Sheet1 (Week 1) in the Project Explorer has to be transferred using copy and paste. This code is identical on all the sheets. The program makes copies of it automatically as new sheets are added by it.

The way to add a sheet is to double-click on the blue arrow on the right of the top row. This will fire up the event procedure in the worksheet's code module which activates the code in the module TXL_4326 which removes all rows with club names marked red and writes the remainder into a new sheet. I didn't change the ID numbers you had in each row.

It's quite a little project and I didn't have the time to test it all. I suggest you start by reading through the comments and then run about 62 tests. Whatever errors you find, let me know about them and I shall try to get them fixed pronto.



I really appreciate the excellent and speedy reply and your answer is amazingly good. Most of what you mention is really way above my head though. I was hoping that as the weeks went on I could keep the previous weeks on view as this is a check to make sure no one picks the same team twice, It would be great if only the discontinued participants were removed and all records remained for all weeks in consecutive worksheets. As I mentioned this could actually go on for anywhere between 10 to 15 weeks and if say in week 5 you could still see weeks 1-4 that would probably be perfect. I really appreciate your time and effort in coming up with this.
wagstaff (rep: 2) May 14, '20 at 10:10 am
Wagstaff, I suggested you run 62 tests and forgot to mention that they should be all different. But you didn't even run one, and that's disappointing. All you are asking is standard fare, already realised in the code I posted.
However, I have posted another version now which includes a counter. Please take the time to look for it.
One more thing that you should know. When you double-click on a sheet other than the last available week the sheet for the week following the one you clicked will be replaced (when you read the comments you should have found "delete existing without notice") . That's very convenient for making corrections in the last week's markings. But any markings that may have already been made on the deleted (over-written) sheet will be irretrievably lost. I might put a break on that if we find a way to cooperate and if it's an issue that you think you might encounter. For now, the cure is not to re-create a sheet which already exists.
Variatus (rep: 4148) May 14, '20 at 8:35 pm
Variatus. I'm sorry if, as I stated, I'm really an absolute beginner so if I haven't followed what you have generously tried to explain I apologise. I am obviously wasting your valuable time and effort so not to waste any more I will close this topic. Thank you for your attention in this.
wagstaff (rep: 2) May 15, '20 at 3:18 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login