Activate workbook based on a range reference.

[S Oberlander]

I have the following little macro as a shortcut on my quick access toolbar.
It works great, however it frequently turns up a different workbook when completed, instead of the workbook I just pasted the data into.
That can get very frustrating when I have 20+ wrkbks open and I have to go find the one I'm working with currently.
Can I somehow use the range captured with the input box to ensure the workbook I need stays on top?

Sub copypastclose()
On Error GoTo quitit 'if input data is invalid or the user clicks cancel it will exit the macro
    Dim Original As Workbook
    Dim DataRange As Range
    Dim Newloc As Range
    Set Original = ActiveWorkbook
    Set DataRange = ActiveSheet.UsedRange
    Set Newloc = Application.InputBox("Select workbook and cell where to copy the data", "Paste", , , , , , 8)
    DataRange.Copy Newloc
    Application.CutCopyMode = False
    Original.Close savechanges:=False
quitit:
End Sub

 

[offthelip]

try this ( untested):

ub copypastclose()
On Error GoTo quitit 'if input data is invalid or the user clicks cancel it will exit the macro
    Dim Original As Workbook
    Dim DataRange As Range
    Dim Newloc As Range
    Set Original = ActiveWorkbook
    Set DataRange = ActiveSheet.UsedRange
    Set Newloc = Application.InputBox("Select workbook and cell where to copy the data", "Paste", , , , , , 8)
    selname = ActiveWorkbook.Name
    DataRange.Copy Newloc
    Application.CutCopyMode = False
    Original.Close savechanges:=False
    Workbooks(selname).Activate
    
quitit:
End Sub

 

[Fluff]

You could also try

Newloc.Parent.Parent.Activate

 

[S Oberlander]

try this ( untested):

    selname = ActiveWorkbook.Name

This is assigning selname to the workbook being closed. I need a variable for the workbook being copied to

 

[S Oberlander]

You could also try

Newloc.Parent.Parent.Activate

Seems to work. Will only know for sure if I never have the issue again
Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.