VB for copying a selected range

[John McCann]

Hi, I am putting in some simple code to copy a selected range from a sheet and paste special into another sheet.

But - I want the vb to be able to recognise what area has been highlighted by the user for copying and on click only paste that data into the new location.

in other words selectedrange.copy etc

but i cannot get this bit of code right

many thanks
John

[tigeravatar]

John McCann,

Welcome to the forum!
Give the following macro a try. You can change rngDest to be the desired location for the paste.

Sub tgr()
    
    Dim rngDest As Range: Set rngDest = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    
    Dim rngCopy As Range
    On Error Resume Next
    Set rngCopy = Application.InputBox("Select the range to copy", "Range selection", , , , , , 8)
    If rngCopy Is Nothing Then Exit Sub
    
    rngCopy.Copy
    rngDest.PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    
End Sub