Previous Topic Next Topic

Letting the user specify which sheet my VBA

[Closed]
Report
-
 Pete 3.1415 -
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

2 replies

Answer 1 / 2
Posts
9
Registration date
Saturday November 15, 2008
Status
Member
Last seen
May 19, 2009
 4
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.
Answer 2 / 2

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
Ask a question