I have now managed to fix part of my problem. However now I have removed the unnecessary quotation marks from Sheets (mySht). Select and I am able to let the user spiffy which work sheet. The remainder of the code has stopped functioning. Yet when run separately in that sheet it works fine. I get a 'select method failure error' at:
A copy of the code below any help or explanation on why this is happening or what I have done wrong is most welcome.
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'
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'
'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
stopno = stopno + 1
'the above code stops the loop'
Application.Goto ActiveWorkbook.Sheets("brain").Range("b38", "y38")
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
'the above code copys the information from the other worksheet and places it on the corret row'