S Oberlander
Board Regular
- Joined
- Nov 25, 2020
- Messages
- 147
- Office Version
- 365
- Platform
- Windows
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?
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?
VBA Code:
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