Excel 2013 VBA: Subscript out of Range (Error 9)
<p>So I have this code:</p>

<pre><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 '&lt;-ERROR here
End Sub
</code></pre>

<p>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.</p>

<p>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</p>

<p>I tried, without any luck:</p>

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

<p>And also modified code with what's now commented out.</p>

<p>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..</p>

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

<p>I am turning to you for help.</p>

<p>Big thanks.
Best Regards.</p>