Insert user defined range into another worksheet

user_6253482 0
Viewed 44

I set a range and can show it with msg box and rng.address but i can't manage to copy/insert or do aynthing with i. In my code snippet i try to copy it but later on i always need to insert the range after the first column in the other sheet.

Here's one particular problem. I am sure it's very easy or i just misunderstand things.I also don't understand 

Dim rng As Range


Set rng = Application.InputBox("Please choose a range", "Obtain Range Object", Type:=8)

 If MsgBox("Your choice " & rng.Address & " ?", vbYesNo, "Confirm") = vbYes Then
    GoTo continue:
    Else
    GoTo retry:
    End If

continue:


Worksheets("Sheet source").Range(rng).Copy Worksheets("sheet destination").Range("A1").Paste
vba excel
2 Answers

You declared Dim rng As Range so is already a range in a specific sheet therefore Range(rng) doesn't work, because Range() awaits an address as parameter (not a range).

Also if you use the .Copy syntax in one line (with destination as parameter) you don't need to .Paste.

It should be 

rng.Copy Worksheets("sheet destination").Range("A1")

Also using Goto (outside an error handing) is a very bad practice. Instead use something like that:

Sub AskMe()
    Dim Rng As Range

    Do
        On Error Resume Next 'next line throws error if user presses cancel so we hide **all** error messages now (and turn them on later!)
        Set Rng = Application.InputBox("Please choose a range", "Obtain Range Object", Type:=8)
        If Err.Number <> 0 Then Exit Sub 'if an error occurred then the user pressed cancel (so we exit)
        On Error GoTo 0 'always re-activate error reporting! Otherwise all error messages stay hidden.
    Loop Until MsgBox("Your choice " & Rng.Address & " ?", vbYesNo, "Confirm") = vbYes
    'repeat asking for a range until yes is pressed

    Rng.Copy Worksheets("sheet destination").Range("A1")
End Sub
user_32196130

To copy the range you could do it as follows:

Sub foo()
Dim rng As Range
retry:
Set rng = Application.InputBox("Please choose a range", "Obtain Range Object", Type:=8)
    If MsgBox("Your choice " & rng.Address & " ?", vbYesNo, "Confirm") = vbYes Then
        GoTo continue:
    Else
        GoTo retry:
    End If
continue:
rng.Copy 
Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteAll
End Sub
user_86703720
Related