copy from one worksheet, paste on another

Pondridge

New Member
Joined
Sep 17, 2012
Messages
4
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,805
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:

VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Seems to work ok for me, unless you hit "cancel". Your code could be simplified a bit.

VBA Code:
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
 

Forum statistics

Threads
1,144,520
Messages
5,724,827
Members
422,581
Latest member
samiak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top