VBA to copy range from one sheet to another.

[plasteredric]

Evening everyone.

I currently have the following code to copy a range of data from one sheet to another. (There will be multiple destination sheets which will all have different names.)

Sub GET_CLEAN_RTR_DATA()
' GET_CLEAN_RTR_DATA Macro
Dim sStartSheet As String
sStartSheet = ActiveSheet.Name


    If UCase(Sheets("RTR_IMPORT").Range("J2")) = "FALSE" Then
        MsgBox "RTR Headers Do Not Match", 0, "RTR Check Error"
        Exit Sub
    End If


Range("RTR_CLEAN_DATARANGE").Copy
Worksheets(sStartSheet).Range("RTR_DATA_PASTEPOINT").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Range("A1").Select
    
End Sub

This works as it is, but for each new sheet another Named range is defined (RTR_DATA_PASTEPOINT) for that sheet.
Is there a better, more efficient way of doing this?

Cheers

[mana]

Option Explicit


Sub GET_CLEAN_RTR_DATA()
     ' GET_CLEAN_RTR_DATA Macro
    Dim destCell As Range
    
    On Error Resume Next
    Set destCell = Application.InputBox("select target cell", Type:=8)
    On Error GoTo 0
    If destCell Is Nothing Then Exit Sub
   
    If UCase(destCell.Parent.Range("J2")) = "FALSE" Then
        MsgBox "RTR Headers Do Not Match", 0, "RTR Check Error"
        Exit Sub
    End If
        
    Range("RTR_CLEAN_DATARANGE").Copy
    destCell.PasteSpecial Paste:=xlPasteValues
     
    Range("A1").Select
     
End Sub

マナ