1

I am an excel vba newbie and having trouble with something simple. I have two excel workbooks; Book 1 and Book 2. I am trying to copy data from particular cells in Book 2 to Book 1. Also Book 2 is a Read Only file and is pwd protected against modifications. I am writing my code in Book 1 and want to set it to a variable. 

Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
wb1.Worksheets("Sheet1").Cells(1, 1).Activate
Set wb2 = Workbooks.Open("C:\Users\self\Desktop\qc\Book2.xlsm")

But I keep getting Subscript Out of Range Error when I try to activate wb1.sheet1 . Also when I run the Set wb2 code on its own, it opens the file but goes directly to the VBA editor of that file and not the spreadsheet. I would be very grateful for any guidance on this vexing problem. Thanks in anticipation.

Community
  • 1
  • 1
user3562650
  • 11
  • 1
  • 2
    I might sound naive here, but I've made this mistake a million times if I've made it once... does `ThisWorkbook` definitely have a `Worksheet` named "Sheet1"? – Dan Wagner Apr 23 '14 at 03:11
  • 2
    I agree with Dan. Also note that if a sheet besides `Sheet1` is active, you'll get a 1004 runtime error next. You have to activate the sheet before you can activate cells on it. Finally, there's a good chance you don't need to activate it anyways, in which case you shouldn't: http://stackoverflow.com/q/10714251/293078. – Doug Glancy Apr 23 '14 at 03:42
  • `Also when I run the "Set wb2" code on its own, it opens the file but goes directly to the VBA editor of that file and not the spreadsheet` I think this happens because you run the code in the VBE. Try running it from `Developer Tab > Macros > Run`. – L42 Apr 23 '14 at 08:36
  • Thanks for your help @DanWagner. I was able to solve it by activating the sheet first. – user3562650 Apr 25 '14 at 00:00
  • Thank @DougGlancy. You were absolutely spot on. I activated the sheet and then selected the cells. It worked like a charm. Thanks for taking the time to reply. – user3562650 Apr 25 '14 at 00:01
  • Thanks @L42. It was exactly as you had said. It works fine by running it from the macros. Appreciate the help. – user3562650 Apr 25 '14 at 00:01

0 Answers0