copy from one worksheet, paste on another

[Pondridge]

Why doesn't this work?

Sub DuesSheet()
Dim vLine As String * 3
vLine = InputBox("Enter Line Number", Value, "2-139")
Sheets("Members").Select
Range("A" & vLine).Select
Selection.Copy
Sheets("Form").Select
Range("F30").Select
ActiveSheet.Paste
End Sub

I'm placing a number in a input box, then going to the members sheet, selecting cell A & the line number, copying contents, then going to the form sheet, cell F30, to paste what was copied. It seems like a simple task, but has been driving me crazy. 14 years since I wrote code and was a novice back then. Please help.

 

[Trebor76]

Hi Pondridge,

Must say I've not seen the bolded part of this Dim vLine As String * 3. What is it for?

In any case this should do what you're after:

Option Explicit
Sub DuesSheet()

    Dim vLine As String

    vLine = InputBox("Enter Line Number:")
   
    If Len(vLine) = 0 Then Exit Sub 'User clicked <Cancel> or made no entry
   
    Application.ScreenUpdating = False
        Sheets("Members").Range("A" & CLng(vLine)).Copy Destination:=Sheets("Form").Range("F30")
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[rlv01]

Seems to work ok for me, unless you hit "cancel". Your code could be simplified a bit.

Sub DuesSheet()
    Dim vLine As String
    vLine = InputBox("Enter Line Number", Value, "2-139")
    If IsNumeric(vLine) And Val(vLine) >= 2 And Val(vLine) <= 139 Then
        Sheets("Members").Range("A" & vLine).Copy Sheets("Form").Range("F30")
    Else
        MsgBox "Error - Entered value is outside of valid range", vbOKOnly Or vbCritical, "Error"
    End If
End Sub