Copy/Paste Special Macro

[3LeggedDog]

Hi,
I'm trying to figure out a macro. There would be a certain range in one sheet, and then, depending on a value given by the user in a dialogue box (with the value being either 1 or 2), that range would then be paste/special/values into one of two spots on a different sheet.

So, for example, I may have range Sheet1!$A$1:$D$5. If the user inputs 1, that range would be copy/paste special/values into Sheet2!$E$1:$H$5, and if the user inputs 2, it would be copy/paste special/values into Sheet2!$A$6:$D$10.

If anybody knows where to start on that, I'd appreciate it.

Thanks,
3LD

 

[jbeaucaire]

Here's one way:

Sub ChoosePastePosition()
Dim r As Long, Rng As Range

Set Rng = Sheets("Sheet1").Range("A1:D5")
r = Application.InputBox("Paste data into range 1 or range 2? (enter 1 or 2 only)", "Select range 1 or 2")

    Select Case r
        Case 1
            Rng.Copy Sheets("Sheet2").Range("E1")
        Case 2
            Rng.Copy Sheets("Sheet2").Range("A6")
        Case Else
            MsgBox "Invalid entry, aborting."
    End Select
End Sub

 

[3LeggedDog]

Thank you very much!

 

[3LeggedDog]

This is great, the only issue is that I need it to do a "Paste Special-Values" rather than just a Paste. Does anyone know what would need to be changed to do that?

Thanks,
3LD

 

[jbeaucaire]

Sub ChoosePastePosition()
Dim r As Long, Rng As Range

Set Rng = Sheets("Sheet1").Range("A1:D5")
r = Application.InputBox("Paste data into range 1 or range 2? (enter 1 or 2 only)", "Select range 1 or 2")

    Select Case r
        Case 1
            Rng.Copy 
            Sheets("Sheet2").Range("E1").PasteSpecial xlPasteValues
        Case 2
            Rng.Copy 
            Sheets("Sheet2").Range("A6").PasteSpecial xlPasteValues
        Case Else
            MsgBox "Invalid entry, aborting."
    End Select
End Sub

 

[3LeggedDog]

Beautiful. Thank you!