Losing the plot with vba

[Closed]
Report
Posts
3
Registration date
Friday January 10, 2014
Status
Member
Last seen
January 10, 2014
-
 Blocked Profile -
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!!

4 replies


"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?
Posts
3
Registration date
Friday January 10, 2014
Status
Member
Last seen
January 10, 2014

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

Bear,
Standby
<strike>It is your second loop statement!</strike>

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
Posts
3
Registration date
Friday January 10, 2014
Status
Member
Last seen
January 10, 2014

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
Blocked Profile
bear,

It is getting late where I am at, so I am going to dream about it tonight.....

Keep doing what you are doing....

Make each seperate thing work on its own! I am certain by the end of the night you would be able to "script" the whole thing together, just by googling different "components" of your solution!

Have Fun.