Private Sub CommandButton3_Click() Dim Xrow As Integer Dim stopno As Integer stopno = 0 Dim mySht As String ' Initialize a String variable mySht = Sheets("brain").Cells(31, 5) MsgBox mySht, , " You Enterd A Report On The Screen Below: " 'It should direct the rest of the coad to the relaven sheet' Sheets(mySht).Select Application.Goto ActiveWorkbook.Sheets(mySht).Cells(1, 1) Xrow = 5 Do While stopno < 1 'starts loop and runs it untill the stopno value is 1' Cells(Xrow, 1).Select 'looks in the A collum of the sheet for the number 1 or higher once it has done' 'that it stops the loop and dose the copy/ paste' If Selection.Value < 1 Then Xrow = Xrow + 1 Else stopno = stopno + 1 'the above code stops the loop' Application.Goto ActiveWorkbook.Sheets("brain").Range("b38", "y38") Application.CutCopyMode = False Selection.Copy Sheets("SS1 A").Select Cells(Xrow, 3).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'the above code copys the information from the other worksheet and places it on the corret row' End If Loop End Sub
DON'T MISS