Want help with a tricky sorting Macro

[Larbec]

I know there are some very talented folks on here, a lot more talent than I or anyone I know. If this can be done would be a great benefit for me and time saver but if it is too difficult then lease tell me and I will continue to sort this row by row. I will try my best to explain with an example

The numbers on sheet "Counter Totals" will come from an external linked sheet. Do not worry about the colors I have as I will do that in conditional formatting (I do not know how to do in a macro)I need a Macro to sort them once they are inputted, Every time a new draw is played I enter a new set f numbers which will give me a new set of these numbers on this sheet. Presently I am entering the new numbers and then copying and pasting each one which is very time consuming



I want to sort Game(s) 45-50 on a new sheet placing the sort as follows and in the sample sheets:



Click the sort button and for example Game 50....



Macro sorts Rows 3 under titles in row 2 E-O1, O1, EEOO1, 50+E-O, 50+EO1, 50+EEOO1, OBE-O, OBEO1, OBEEOO1 (on new sheet)
Row 12 under titles in row 11 E-O2, O2, EEOO2, 50+E-O, 50+EO2, 50+EEOO2, OBE-O, OBEO2, OBEEOO2 (on new sheet)
Row 21 under titles in row 20 E-O3, O3, EEOO3, 50+E-O, 50+EO3, 50+EEOO3, OBE-O, OBEO3, OBEEOO3 (on new sheet)
Row 30 under titles in row 29 E-O4, O4, EEOO4, 50+E-O, 50+EO4, 50+EEOO4, OBE-O, OBEO4, OBEEOO4 (on new sheet)
Row 39 under titles in row 38 E-O5, O5, EEOO5, 50+E-O, 50+EO5, 50+EEOO5, OBE-O, OBEO5, OBEEOO5 (on new sheet)
Row 48 under titles in row 47 E-O6, O6, EEOO6, 60+E-O, 60+EO6, 60+EEOO6, OBE-O, OBEO6, OBEEOO6 (on new sheet)
Then places them on a new sheet under their Game numbers (40-41-42-43-44-45-46-47-48-49-50)



The tricky part (I think) will be to increment the new numbers down a row on the new sheet. Is this possible? As I mentioned above do not worry about the color coding. I need the numbers and can add the colors later

14786 14787

14785

Thank you for all your help thus far and REALLY appreciate all you do for us and myself

[Larbec]

Maybe this will better explain. I have cell locations listed. I basically want to copy and paste rows to a new sheet and when a new set of numbers come in on sheet "counter total" copy and paste them to the same cell locations on the new sheets Games#s but I want to move all the ones presently there down a row so B3 moves to B4 and so on

copy and paste:
B3:AV3 to a new sheet (Game 50) same cell location
B4:AV4 to a new sheet (Game 49) same cell location
B5:AV5 to a new sheet (Game 48) same cell location
B6:AV6 to a new sheet (Game 47) same cell location
B7:AV7 to a new sheet (Game 46) same cell location
B8:AV8 to a new sheet (Game 45) same cell location

B12:AV12 to a new sheet (Game 50) to B50:AV50
B13:AV13 to a new sheet (Game 49) to B50:AV50
B14:AV14 to a new sheet (Game 48) to B50:AV50
B15:AV15 to a new sheet (Game 47) to B50:AV50
B16:AV16 to a new sheet (Game 46) to B50:AV50
B17:AV17 to a new sheet (Game 45) to B50:AV50

B21:AV21 to a new sheet (Game 50) to B100:AV100
B22:AV22 to a new sheet (Game 49) to B100:AV100
B23:AV23 to a new sheet (Game 48) to B100:AV100
B24:AV24 to a new sheet (Game 47) to B100:AV100
B25:AV25 to a new sheet (Game 46) to B100:AV100
B26:AV26 to a new sheet (Game 45) to B100:AV100

B30:AV30 to a new sheet (Game 50) to B150:AV150
B31:AV31 to a new sheet (Game 49) to B150:AV150
B32:AV32 to a new sheet (Game 48) to B150:AV150
B33:AV33 to a new sheet (Game 47) to B150:AV150
B34:AV34 to a new sheet (Game 46) to B150:AV150
B35:AV35 to a new sheet (Game 45) to B150:AV150

B39:AV39 to a new sheet (Game 50) to B200:AV200
B40:AV40 to a new sheet (Game 49) to B200:AV200
B41:AV41 to a new sheet (Game 48) to B200:AV200
B42:AV42 to a new sheet (Game 47) to B200:AV200
B43:AV43 to a new sheet (Game 46) to B200:AV200
B44:AV44 to a new sheet (Game 45) to B200:AV200

B48:AV48 to a new sheet (Game 50) to B250:AV250
B49:AV49 to a new sheet (Game 49) to B250:AV250
B50:AV50 to a new sheet (Game 48) to B250:AV250
B51:AV51 to a new sheet (Game 47) to B250:AV250
B52:AV52 to a new sheet (Game 46) to B250:AV250
B53:AV53 to a new sheet (Game 45) to B250:AV250

[nilem]

Hi Larbec,
How's the weather in Texas? :hi:

maybe something like this

Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
x = .Range("A2:AV" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
If (x(i, 1)) = "Game" Then j = j + 1
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 48).Value = Application.Index(x, i, 0)
End With
End If
Next i
End Sub

[snb]

First you will have to add the sheets "Game48", "Game49" and "Game50"


Sub M_snb()
sn = Sheet1.Cells(2, 1).Resize(52, 48)

For j = 0 To 5
Sheets("Game" & sn(2 + j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(6, 48) = Application.Index(sn, [2+(row(1:6)-1)*9], [transpose(row(1:48))])
Next
End Sub

[Larbec]

Hi Larbec,
How's the weather in Texas? :hi:

maybe something like this

Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
x = .Range("A2:AV" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
If (x(i, 1)) = "Game" Then j = j + 1
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 48).Value = Application.Index(x, i, 0)
End With
End If
Next i
End Sub


I edited this post. I verified and seems to be working perfectly I will still build it in a new book and see how it dies but so far I've done 10 new rows and PERFECT. As for the weather lol, we went from Summer of 90 degrees to 50 in less than 2 hours LOL, raining for now but love Texas.

The only think I see that "may be" an issue is I never clear out the row numbers. They are linked to another book so I will not use the clear button. Should we remove it altogether from the code?

Also, will it be an easy fix to add Games 10-44 on my own or can I give you the cell locations? The complete MASTER book has 10 Games 40-50. Again, THANK YOU

[Larbec]

First you will have to add the sheets "Game48", "Game49" and "Game50"


Sub M_snb()
sn = Sheet1.Cells(2, 1).Resize(52, 48)

For j = 0 To 5
Sheets("Game" & sn(2 + j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(6, 48) = Application.Index(sn, [2+(row(1:6)-1)*9], [transpose(row(1:48))])
Next
End Sub

Im not sure I follow you. When tested it seems to be working fine. I'll triple check though and see what I may be missing.

Thanks for helping

[snb]

Please do not Quote.

Well, I'm sure you are not following me. ;)

[nilem]

Aabout adding new games. Try to create new sheets "Game10", "Game11" etc. and copy titles such as

"Game
E-O1

EO1

EEOO1


50+E-O1..."


from "Counter Totals" into the right lines. I hope it will work without any issues.
"Clear Games" button works for sheets "Game..." only.
And we have -5 degrees C (about 23 degrees F), and a lot of snow :)

SNB, I will follow you. Nice code :yes

[snb]

@Nilem

I know you would be one of the few.... ;)

[Larbec]

Ah, snb...is thta why it does not copy and paste the other Games?

Nilem,

When it copies and paste it only does Game 50 correctly, the other games when I new set of numbers are added it pushes down the same number all the time and no new numbers are there

[snb]

My code does what you asked for.
For new requirements it needs to be adapted.

[Larbec]

Ah, snb...is that why it does not copy and paste the other Games?

I will change the code and try, thanks Snb, I will not quote you ever again, but I may have to quote you down the road for your intelligence (-:

[nilem]

...when I new set of numbers are added it pushes down the same number all the time...
Larbec, if you change the value, from which it takes the old data? Please check again

[Larbec]

Nilem,

This is what the other games are doing.

14794

[Larbec]

Snb,

Is this correct or do they go in separate modules?


Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
x = .Range("A2:AV" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
If (x(i, 1)) = "Game" Then j = j + 1
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 48).Value = Application.Index(x, i, 0)
End With
End If
Next i
End Sub

Sub M_snb()
sn = Sheet1.Cells(2, 1).Resize(52, 48)

For j = 0 To 5
Sheets("Game" & sn(2 + j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(6, 48) = Application.Index(sn, [2+(row(1:6)-1)*9], [transpose(row(1:48))])
Next
End Sub

[snb]

Please post a workbook that contains the data for your new requirements.

[Larbec]

This is how the whole thing works and if I can get it automated or partly that would be the next thing to slice bread for me as it would save me a tremendous amount of time.

The Input sheet has numbers in I2:N101 (there is also an array in B2:G101) when a new number is entered I am manually moving the numbers down 1 row because my Reset Macro is not working correctly. So row 2 I2:N2 numbers now go to row 3 I3:N3 , row 3 I3:N3 goes to row 4 I4:N4 and so on all the way to row 101 I101:N101. The numbers in row I101:N101 gets dropped off as the new Numbers are added. I only keep 100 rows at one time.

These new draw numbers come from the sheet called DRAW B13:G2416. My goal is to place all these DRAW numbers in to the Input sheet resetting them back to Game 49 row 3 I3:N3 every time and load a new number into Game 50 row 2 I2:N2 from the DRAW book

Presently I am copying 101 rows from the Draw sheet and pasting them to the Input sheet 1 row at a time moving the rows down 1 row each time I add a new number (i hope this makes sense)

Every time a new number is entered to the Input sheet they go to external linked sheets that come back and give me other numbers that are in the sheet called Counter Totals (the one I originally posted about)

At this point, this is the Macro I originally asked about thinking if I asked for all of this at once I would only confuse ppl. Kind of like biting off an elephant.

The Macro that Nilem wrote does exactly what I wanted but only Game 50 works correctly. The other games were duplicating the original number and that's when you chimed in (-:

I am attaching a shortened workbook with all 10 games so you can see it all. What ever part of it you can help with as always is MUCH appreciated. What you and others do simply amaze me and I am trying my best to learn as much as I can from y'all but its coming slower than i want. This stuff is hard lol but I am sure the light bulb will kick on eventually

Please let me know if you have any questions. The color coding on my sheets are not important, I can add that later

Thank you so much for looking at this and please tell me if this will be too time consuming I will continue to do things manually. I do value your time as well as others and I have no clue how difficult this is to do. For me, extremely. I have a feeling for you its probably quite simple (once explain it clearly) as you are a GURU and really brilliant with this stuff

[nilem]

Larbec,
my last attempt. I want to show how the code works for me here (https://www.dropbox.com/s/zmbwnd7o6dmxf0d/Film%20%26%20Soft.zip?dl=0)

[Larbec]

Nilem,

I think I followed what you are doing. Please don't give up on me. I REALLY appreciate your patience. I'm trying my best to explain. When we hit the sort button whatever numbers are in each game # needs to go in that Game #. Example:

Game 50 "Counter Totals" sheet needs to copy what is in Rows 3,17,31,45,59,73 to the Game 50 sheet Rows 3,51,101,151,201,251.

Game 49 "Counter Totals" sheet needs to copy what is in Rows 4,18,32,46,60,74 to the Game 49 sheet Rows 3,51,101,151,201,251.

Game 48 "Counter Totals" sheet needs to copy what is in Rows 5,19,33,47,61,75 to the Game 49 sheet Rows 3,51,101,151,201,251.

and so on. I do not think that's what is happening in the video. If I am incorrect my apologies. Again thank you for helping me and your patience. Were almost there Nilem! Your first attempt was what we need with the exception of the extra games and the row numbers not duplicating as they did in post #14 (incorrect). Post #12 pic is correct. As I enter a "new" draw a new set of numbers will appear in each Game # on the "Counter Total" sheet Those new numbers that are in the "Counter sheet" will be copied and pasted to the Game# sheets moving the last numbers down a row.

If we look at what is in Game 50 sheet for example Rows 3,51,101,151,201,251. These will move to Rows 4,52,102,152,202,252 and the new set pasted to Rows 3,51,101,151,201,251

[snb]

@Larbec

'Structuring precedes coding'
That's exactly what is lacking in your workbook.
Writing data to 3,51,101, etc. is 'bad' design. (3,53,103,153 would be somewhat better, but what after the next 'draw')
It complicates matters in terms of coding unnecessarily.

Please rethink the purpose of your workbook first of all.

[Larbec]

@snb,

thank you for your response. I'm glad you told me this as I have no clue what makes a bad situation yet

it does not matter what row the data is placed in. What I'm trying to accomplish is when I enter a new draw number into the Input Book and new numbers occur in the "Counter Totals" sheet. I want to sort them into their own game sheet. If 3,53,103,153 is better that fine with me. I pulled the 1, 51, 101 out of thin air. I will have between 1500 and 2500 draws to enter and will end up either insert more rows or cut and pasting once a specific area gets filled ie 3,53,103,153. Perhaps I should spread the 6 locations out to 500 or 1000 rows What I'm saying is each Game for example 3, 1003, 2003, 3003, 4003, 5003 . You are by far the expert when it comes to this and please feel free to make any suggestions or ideas. Again, placement of rows is not a concern as long as the 6 numbers are in an area to themselves per a Game Number (E-01, EO1, EEOO1, 50+E-O1, 50+ EO1, 50+ EEOO1, OBE-O1, OBEO1, EEOOB001) (E-02, EO2, EEOO2, 50+E-O2, 50+ EO2, 50+ EEOO2, OBE-O2, OBEO2, EEOOB002) ....and so on.... To #6


Thank you so very much

[Larbec]

Nilem,

I feel really really bad for driving you both crazy. This Macro works and works well. Between having the flu and work I made mistakes when i entered your Macro. Here is the entire amount of Games from 40-50. If I could fix your Macro to incorporate the others I would. Can you please make the necessary adjustments for me? Again, my apologies and thank you for your assistance with this and patience with me. :banghead: If it is easier to do it the way Snb suggest that's fine too. Please make this as easy as possible for yourself

When I run the Macro in the sheet with more games it gives me a run time 9 error and when I go to debug here is where it stops. I do not know what to do. Perhaps you can explain what is going on so I know for next time and put this all in my notes so I do not have to bother you all with the same type error.

Thanks

14809 14810

[Larbec]

@snb

Here is a new book, let m e know if this will work and again thank you

Here is an example of Game 48,49,50 and how they are laid out. All the other games follow suit

14813

[Larbec]

I have made some corrections from the Game 40-50 book to this one. Use this book please

[nilem]

Larbec, rename sheets Game45, Game46, Game47 with no space at the end. Then the code will work correctly

[Larbec]

Nilem, thank you. That took care of it running all the way through . I will check this out later on today when I get off work. Again THANK YOU!!!

[snb]

First: remove all 'merged cells' in sheet1.

use:


Sub M_snb()
sn = Sheet1.Cells(2, 1).Resize(83, 48)

For j = 0 To 5
Sheets("Game" & sn(2 + j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(6, 48) = Application.Index(sn, [2+(row(1:6)-1)*14], [transpose(row(1:48))])
Next
End Sub

look at row 505 in each sheet.

[Larbec]

Thanks Snb and thanks Nilem for all your patience and time spent on this. This is going to save me a lot of time and mistakes. I tried to add to your reputation but it says I have to spread the love. You guys deserve more.

[snb]

I wasn't even aware I had any.

I think you can award anyone any time.

[Larbec]

Here's a snip

14819

[snb]

Just ask SamT

[Larbec]

I sent him a PM asking