CODEWITHSUNDEEP
vbaexcelexcel-2010
Nick
Nick

Reputation: 1

Copy and Paste using Input Box in Excel 2010 - VBA

Help needed. I'm a newbie to VBA and trying to start with simple macro. But even with that failing miserably. All i'm trying to do is copy and paste from one worksheet to another using an input box to specify the range to be copied. Now I know for the input box is:

Application.InputBox("Enter the range from which you want to copy : ", Type:=8)

But what do line do i need in order to copy to a cell in another worksheet?

I apologise in advance for being an idiot.

Upvotes: 0

Views: 8390

Answers (3)

peterm
peterm

Reputation: 92785

One way to do it is like this:

Sub CopyRange()
    Dim FromRange As Range
    Dim ToRange As Range

    Set FromRange = Application.InputBox("Enter the range from which you want to copy : ", Type:=8)
    Set ToRange = Application.InputBox("Enter the range to where you want to copy : ", Type:=8)

    FromRange.Copy ToRange
    'Or you can do it like this if you need some flexibility on Paste 
    'FromRange.Copy
    'ToRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
End Sub

Upvotes: 1

SeanC
SeanC

Reputation: 15923

Here's some sample code:

Option Explicit

Sub CopyStuff()

Dim x As Range
Dim y As Range

Set x = Application.InputBox("Select what copy using the mouse", Type:=8)
Set y = ActiveWorkbook.Sheets("Sheet2").Range("A1")

x.Copy y

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim rngCopyFrom As Range

    On Error Resume Next
    Set rngCopyFrom = Application.InputBox("Enter the range from which you want to copy", Type:=8)
    On Error GoTo 0

    If Not rngCopyFrom Is Nothing Then
        '~~> Copy the range to say Cell A1 of Sheets("weekly raw")
        rngCopyFrom.Copy ThisWorkbook.Sheets("weekly raw").Range("A1")
    End If
End Sub

Upvotes: 2

Related Questions