Hi Tizek,
. Before I go any further, I‘ll show You what I have up until now, as I was not too sure exactly what you wanted with regard to
……….What i dont want it to do however is produce duplicates on the seperate tab if this makes sense…….
. Also, like Alan_P, I was a bit confused with the Team name inconsistencies. But I got over that one: The file that I send back to you now only has 3 teams for demonstration Purposes. However to add a new Team you simply open a new Tab and give it the Team Name: Note It is important that it is exactly the team name as spelt in column Club in Sheet 1 (TKDEL_Membership). So copy the name from that column and paste it in the Tab name. Then when you run the code, it automatically takes account of the new team. You can keep adding teams as many as you like (within reason!-26 is certainly no problem!).
. There are lots of easy tidying up that can be done to the code, once I know if it is OK and a bit more of exactly what you want.
. Take a look at what I have done so far and Let me know how you get on. I’ll (We’ll) take it then further when we Know exactly what you want.
. Here is the code which I have put in Sheet1 (TKDEL_Membership) Module.
<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">'Not necerssary but a good idea</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> SimpleSortOfSort1()<br><SPAN style="color:#00007F">Dim</SPAN> HeadingNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, HeadingNumberMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Define variable as integer up to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> HeadingNumberMax = Worksheets("TKDEL_Membership").Cells(1, Columns.Count).End(xlToLeft).Column <SPAN style="color:#007F00">'Get Maximun Heading number</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TKDEL_MembershipRowMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> <SPAN style="color:#007F00">'Define maximum Row in sheet 1 as integer up to 32,767</SPAN><br><SPAN style="color:#00007F">Let</SPAN> TKDEL_MembershipRowMax = Worksheets("TKDEL_Membership").Cells(Rows.Count, 1).End(xlUp).Row <SPAN style="color:#007F00">'get maximum rows for Sheet1</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TKDEL_MembershipRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> NumberOfTeams <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br><SPAN style="color:#00007F">Let</SPAN> NumberOfTeams = Worksheets.Count <SPAN style="color:#007F00">'Get number of worksheets.</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> WorksheetNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TeamName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">' Define Team name as a string variable</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TeamWorksheetRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Let</SPAN> TeamWorksheetRow = 1<br> <SPAN style="color:#00007F">For</SPAN> WorksheetNumber = 2 <SPAN style="color:#00007F">To</SPAN> NumberOfTeams <SPAN style="color:#007F00">'Go through each team...</SPAN><br> TeamName = Worksheets(WorksheetNumber).Name <SPAN style="color:#007F00">' ...get it's name...</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> TeamWorksheetRow = 1 <SPAN style="color:#007F00">' Start at row 1 in team worksheet(each time)</SPAN><br> <SPAN style="color:#00007F">For</SPAN> TKDEL_MembershipRow = 2 <SPAN style="color:#00007F">To</SPAN> TKDEL_MembershipRowMax <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">'... go through every Row in First sheet</SPAN><br> <SPAN style="color:#00007F">If</SPAN> Worksheets("TKDEL_Membership").Cells(TKDEL_MembershipRow, 7).Value = TeamName <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'..check for team name match</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> TeamWorksheetRow = TeamWorksheetRow + 1 <SPAN style="color:#007F00">' goto next Row in team Worksheet</SPAN><br> <SPAN style="color:#00007F">For</SPAN> HeadingNumber = 1 <SPAN style="color:#00007F">To</SPAN> HeadingNumberMax <SPAN style="color:#00007F">Step</SPAN> 1<br> Worksheets(WorksheetNumber).Cells(TeamWorksheetRow, HeadingNumber).Value = Worksheets("TKDEL_Membership").Cells(TKDEL_MembershipRow, HeadingNumber).Value <SPAN style="color:#007F00">'Copy entire row</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> HeadingNumber<br> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' Otherwise if no match.....</SPAN><br> <SPAN style="color:#007F00">' Do nothing!</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> TKDEL_MembershipRow <SPAN style="color:#007F00">' go to next row in sheet 1 untill all rows are done...</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> WorksheetNumber <SPAN style="color:#007F00">' then go to next Team sheet</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'SimpleSortOfSort1()</SPAN></FONT>
. Here are two files, one before and one after running the macro
http://snk.to/f-ctpadfik
FileSnack | Easy file sharing
. For now the main thing is to see if it comes out roughly how you want it and suggest any improvements.
Be in touch tomorrow
Alan
………………………………………………………………………………………………….
Hi Alan_P,
Hi Alan_S, looks interesting this one! Hope you don't mind if I have a go as well?
. I guess it is fair game for everyone to try? (Just hope a profi Puts us right in the end!!) Give it a go, or take it further- It is bed time here now so I’ll have a look how you got on tomorrow sometime.
God Night, Gute nacht