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



マナ