0

I am trying to copy a range from one workbook to another. However I get a run-time error that my subscript is out of range. This is the offending line:

Set wbDest = Workbooks("newFile.xlsx")

What am I missing?

Code

Dim wbDest As Workbook
Dim ws2 As Worksheet

Set myRange = Range("A1", Cells(LastRow, LastCol).Address)' Values retrieved from function
Set wbDest = Workbooks("newFile.xlsx")'both newFile and Thisworkbook stored on my desktop
Set ws2 = wbDest.Sheets("Sheet 1")
ws2.Range("A1") = wsJournals.myRange.Value
0m3r
  • 12,286
  • 15
  • 35
  • 71
Calgar99
  • 1,670
  • 5
  • 26
  • 42
  • possible duplicate of [VBA: Subscript out of range](http://stackoverflow.com/questions/25313021/vba-subscript-out-of-range) – John Alexiou Aug 15 '14 at 11:17

1 Answers1

2

When you open a workbook, you need to make sure you give the entire path of the workbook and also open it. Following is the syntax,

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open(Filename:="C:\Users\user1\Desktop\Book1.xlsx")
Set ws = wb.Sheets("sheet1")

Then you can use the ws to choose specific cells and do other stuff. It worked for me.

John Alexiou
  • 28,472
  • 11
  • 77
  • 133
sdwaraki
  • 382
  • 1
  • 4
  • 12