CODEWITHSUNDEEP
excelvba
Shevy
Shevy

Reputation: 57

Inputbox in [excel] [vba] to select range from a different workbook

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?

Upvotes: 0

Views: 711

Answers (1)

David Zemens
David Zemens

Reputation: 53663

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

Upvotes: 1

Related Questions