losing the plot with vba Closed

Question

hello and huge thanks in advance, the following is a macro i have adapted to try and get a centralised team sheet auto sort and copy the relevant rows into the corresponding sheet. the code::-- Sub TEAM() 'Start on the main first sheet, cell A2 Sheets("Team").Select Range("A2").Select Do While ActiveCell <> "" 'You need to create these for every possible extension ' Also, create individual worksheets for each If InStr(1, ActiveCell, "1", 1) <> 0 Then ActiveCell.EntireRow.Copy Sheets("Senior XV").Select Range("A2").Select If InStr(1, ActiveCell, "A", 1) <> 0 Then ActiveCell.EntireRow.Copy Sheets("Army A").Select Range("A2").Select ElseIf InStr(1, ActiveCell, "23", 1) <> 0 Then ActiveCell.EntireRow.Copy Sheets("Army U23").Select Range("A2").Select Else 'If it's not an extension you have specified, it highlites the cell ActiveCell.Interior.ColorIndex = 6 GoTo SKIPPING End If Range("A2").Select 'Loops down until there's an open Do While ActiveCell <> "" ActiveCell.Offset(1, 0).Select Loop ActiveSheet.Paste 'Go back to the starting sheet & iterate to the next row Sheets("Team").Select SKIPPING: ActiveCell.Offset(1, 0).Select Loop End Sub so, in other words, i have a master sheet with 90 odd players details in the same work book i have separate sheets corresponding to the differing team placements i.e senior-xv, a team, u23s. when i write in column 'a' that, that player is in a particular grouping (u23) his details are copied into the corresponding sheet in the next available row. i would also, if possible like it to automatically do this and should a player placement change (i.e. player is selected for the a team instead of the u23) his info is deleted from the old sheet and put in the new one. hope you guys can help!!

ac3mark · Answer

"bear",

So, where is the failure?  We can help, but do not wish to duplicate what you have already completed, what do you need help with?

thebear3866 · Answer

sorry, it keeps coming up with "compile error: loop without do"

ac3mark · Answer

Bear, Standby ~~It is your second loop statement!~~ OK, try not to use GOTO statements! I believe, you are exiting the loop with the goto, and therefore, the loop breaks. Whatever you want goto to perform, make it a seperate function or routine, and have it return the value back inline! So it would look like: activecellcolor=6 changethecolor(activecellcolor) instead of: ActiveCell.Interior.ColorIndex = 6 GoTo SKIPPING Now build a subroutine called changethecolor(whatcolor) that accepts the whatcolor variable and places it into the activecell.interior.colorindex property. Try that! //ark -Moderator/Contributor

thebear3866 · Answer

thats cool it is doing something but it isn't copying rows over, i am an absolute newbie at vba and apart from the sheets and range names i haven't a clue whats going on, i'm trying to get this workbook working so that the three team managers can sit down and then when they decide who goes where, it auto populates each sheet.

thank you so much for your help so far

Losing the plot with vba

4 responses

Similar discussions

Subscribe To Our Newsletter!