Excel 2013 VBA: Subscript out of Range (Error 9)

Question

So I have this code:

Sub CopyItems()
    Dim Source As String
    Dim Target As String

    'Dim SourceSheet As String
    'Dim TargetSheet As String

    Source = "Source.xlsm"
    Target = "needChange.xlsm"

    'SourceSheet = "Sprint backlog"
    'TargetSheet = "Sheet1"

    Workbooks(Source).Sheets("Sprint backlog").Range("B6:B15").Copy
    Workbooks(Target).Sheets("Sheet1").Range("A14:A23").Paste '<-ERROR here
End Sub

And it's giving me the Run-time error '9' as expressed in the title. The code is so simple that I am completely stumped. I read around the net and it seems it's because of names that don't exist, however both the sheets and workbooks exist, with identical names. There is no space or weird char between any of the code.

Basically I want to Copy a column ranging from B6 to B15 from the sheet "Sprint backlog" in Source.xlsm to the range A14 to A23 in Sheet1 of needChange.xlsm

I tried, without any luck:

Workbooks(Source).Sheets("Sprint backlog").Range("B6:B15").Copy _
Workbooks(Target).Sheets("Sheet1").Range("A14:A23").PasteSpecial

And also modified code with what's now commented out.

I suspect the Macro can't access the target file (needChange.xlsm) because it can't find it or can't access it and therefore return the problem, but i cannot figure out how to fix it with code..

If it helps, while running the macro, both of the Workbooks in this code were open and accessible for me.

I am turning to you for help.

Big thanks. Best Regards.

Either the workbook is not called "needChange.xlsm", the sheet is not called "Sheet1", or you have some other code running in a Change event that has an error in it. — Rory, Feb 29 '16 at 14:18
fwiw, just use the top-left cell of your destination as the target range. `.Range("A14")` will adjust to the size and shape of the source; you do not need `.Range("A14:A23")`. — , Feb 29 '16 at 14:24
Take a look at this[http://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another] — dev1998, Feb 29 '16 at 14:43
To pin-point the error, you could work with intermediate objects and/or use the Immediate window while debugging. Like i.e. `Dim wb As Workbook, Set wb = Workbooks(Target), Dim sh As Worksheet, Set sh = wb.Sheets("Sheet1")`, etc. — Carl Colijn, Feb 29 '16 at 15:04
Rory - [img](https://i.gyazo.com/5ff78c64809c25d73e065d44bd4337e0.png) I triple checked, the sheet names are identical :( Jeeped - thanks, ill keep that in mind. dev1998 - I saw that, the thing is.. when this code is final and is going to be used the path might not be the same. — this.guy, Feb 29 '16 at 15:05
Temporarily re-write your code to test each part of the "path" to see what Excel is complaining about. For example, write `Debug.Print Workbooks(Target).Name`, then `Debug.Print Workbooks.(Target).Sheets("Sheet1").Name`, etc. That can help you pinpoint the problem. — PeterT, Feb 29 '16 at 15:47

score 0 · Accepted Answer · answered Feb 29 '16 at 19:36

This was trickier than expected. I borrowed heavily from this web page http://ccm.net/faq/24666-excel-vba-copy-data-to-another-workbook.

I had to add references to the sheets for the copy and paste to get it to work.

The code as posted requires both workbooks to be open, but you can have wbTarget get opened if you give it a pathname. In that case you could comment out the two lines that appear after the -OR-.

The code can also save and close the target workbook as well.

Sub CopyOpenItems()
   '
   ' CopyOpenItems Macro
   ' Copy open items to sheet.
   '
   ' Keyboard Shortcut: Ctrl+Shift+O
   '
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted
   Dim wbThis              As Workbook 'workbook from where the data is to copied
   Dim strName             As String   'name of the source sheet/ target workbook

   'set to the current active workbook (the source book)
   Set wbThis = ActiveWorkbook

   'get the active sheetname of the book
   strName = ActiveSheet.Name

   'open a workbook that has same name as the sheet name
   'Set wbTarget = Workbooks.Open("C:\YourPath\needChange.xlsm")

    ' - OR -
    Workbooks("needChange.xlsm").Activate
    Set wbTarget = ActiveWorkbook


   'select cell A1 on the target book
   'wbTarget.Range("A1").Select

   'clear existing values form target book
   'wbTarget.Range("A1:M51").ClearContents

   'activate the source book
   wbThis.Activate

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'copy the range from source book
   wbThis.Sheets("Sprint backlog").Range("B6:B15").Copy

   'paste the data on the target book
   wbTarget.Sheets("Sheet1").Range("A14").PasteSpecial

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'save the target book
   'wbTarget.Save

   'close the workbook
   'wbTarget.Close

   'activate the source book again
   wbThis.Activate

   'clear memory
   Set wbTarget = Nothing
   Set wbThis = Nothing

End Sub

Hi - I used your code and modified it a bit - but now everything works good. Thanks :) — this.guy, Mar 01 '16 at 16:06
no need to activate or select the workbooks, they just need to be opened at the copy time. — Patrick Lepelletier, Mar 02 '16 at 09:30

Patrick Lepelletier · Answer 2 · 2016-03-02T09:40:20.973

if you copy only values (and no formula, picture, formating), a simple

Workbooks(Target).Sheets("Sheet1").Range("A14:A23").value = Workbooks(Source).Sheets("Sprint backlog").Range("B6:B15").value is good.

(in one same code line, only the size of the window here makes it look beeing on 2).

for more than values :

Workbooks(Source).Sheets("Sprint backlog").Range("B6:B15").Copy _ Workbooks(Target).Sheets("Sheet1").Range("A14:A23").

(in 2 lines)

note : the _ means that the folowing line is meant to be on the same line , and is only there for reading the code more easily purpose . (you made that error in your second code)

note 2 : range().paste does not exist , only sheets().paste , or range().pastespecial.

note 3 : of course, all worbooks, and sheets, must exist and have the same exact name than those used...

note 4 : copy/paste works ONLY if both workbooks are already opened. for closed files it's a different story.

To make it short, you made 2 mistakes : _, and range().paste.

Excel 2013 VBA: Subscript out of Range (Error 9)

2 Answers2