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.

Answer 1

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