Letting the user specify which sheet my VBA Closed

Question

Hello I have been using many of your very useful articles in the past and they have allowed me to solve many problems. However I am now Very stuck despite research the problem for some time. 

I am trying to complete a piece of code  that cuts and past data on to a sheet that is specified by the user. The data is then placed on the next available free row. I am relatively new to VBA so apologies if this code is badly presented or completely wrong. 

I need this macro to pick up a name of a sheet from a cell on  "brain" in a cell  "E31"
This cells contents will change depending on the users selection. 

Next I need the code to select the specified sheet and run the remaining code.

Any help on this matter will be greatly appreciated especially if I am going about this in the complete wrong way.

Private Sub CommandButton3_Click()


Dim Xrow As Integer
Dim stopno As Integer
stopno = 0
Dim mySht As Single
Application.Goto ActiveWorkbook.Sheets("brain").Range("E31")

mySht = Selection.Value



Cells(1, 1).Select
Xrow = 5





Do While stopno < 1

'starts loop and runs it until the stopno value is 1'

Cells(Xrow, 1).Select

'Looks in the A column 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 copies the information from the other worksheet and places it on the correct row'

End If

Loop



End Sub

zipun · Answer

hi,
try these links to get help.
these are tutorials that can help you:
http://www.your-save-time-and-improve-quality-technologies-online-resource.com/...
http://www.excel-vba.com/excel-vba-contents.htm
hope they help you.

Pete 3.1415 · Answer

Thanks Zipun

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: 

Cells(Xrow, 1).Select

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'

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

Letting the user specify which sheet my VBA

2 responses

Similar discussions

Subscribe To Our Newsletter!