0

When I go through my sub line by line it works, but when i just click run it stops after the input box and gives me"run-time error '1004': Activate method of range class failed" for Newloc.Select

Dim Original As Workbook
Dim Newloc As Range
Dim Newbk As Workbook
Set Original = ActiveWorkbook
Set Newloc = Application.InputBox("Select workbook and cell where to copy the data", "Paste", , , , , , 8)
Newloc.Select
Set Newbk = ActiveWorkbook
Original.Activate
ActiveCell.CurrentRegion.Copy
Newbk.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Original.Close savechanges:=False

How can I get this to work with the ribbon shortcut I created?

Shevy
  • 57
  • 2
  • 10

1 Answers1

1

The problem is that you can't select a range in a workbook that is not yet active. The deeper problem is that you should not rely on Activate/Select in VBA for Excel. :)

Solution is to refactor your code so that you don't need to rely on activating sheets back & forth:

Dim Original As Workbook
Dim srcRange As Range
Dim Newloc As Range
Dim Newbk As Workbook
Set Original = ActiveWorkbook
Set srcRange = ActiveCell.CurrentRegion

Set Newloc = Application.InputBox("Select workbook and cell where to copy the data", "Paste", , , , , , 8)

srcRange.Copy Newloc
Application.CutCopyMode = False
Original.Close savechanges:=False
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/198619/discussion-on-answer-by-david-zemens-inputbox-in-excel-vba-to-select-range-f). – Samuel Liew Aug 29 '19 at 00:21