Copy range from an active workbook to a closed workbook, MAC OSX, ERROR

Question

User Peh gave me great code to copy data from a closed workbook to an open workbook, but now I want to do the reverse. I want to have an open workbook with formula and values I want to copy paste these as values into a closed workbook. I want to copy range B36:K36 from the "AllData" tab in my open workbook to paste values starting in K1 in Sheet1 of a closed workbook called "archive.xlsx".

This is my current code (from the internet):

Sub CopynPasteWrkBk()
    Dim InputFile As Workbook
    Dim OutputFile As Workbook
    Dim Inputpath As String
    Dim Outputpath As String '

    ' Set path for Input & Output
    fileInputpath = "/Users/cie/Desktop/macrotest/"
    Outputpath = "/Users/cie/Desktop/macrotest/"

    '## Open both workbooks first:
    Set InputFile = ActiveWorkbook
    Set OutputFile = Workbooks.Open(Outputpath & "archive.xlsx")

    'Now, copy what you want from InputFile:
    InputFile.Sheets(“AllData”).Activate
    InputFile.Sheets(“AllData”).Range("B36:K36").Copy

    'Now, paste to OutputFile worksheet:
    OutputFile.Sheets("Sheet1").Activate
    OutputFile.Sheets("Sheet1").Range("k1").PasteSpecialOutputFile.Save

    'Close InputFile & OutputFile:
    InputFile.Close
    OutputFile.Close
End Sub

When I execute this code in my active workbook, it successfully opens up "archive.xlsx", gives me a runtime error 9 subscript out of range and errors out, then I see that it has selected selects K1 in my active workbook (which is the cell in which I want to start pasting in the "archive.xlsx" workbook). What is wrong? Both files live in the same folder on my desktop.

Which line gives the error? My guess is the one which mentions “AllData”, but you should tell us so that we don't have to all guess (and all guess something different). — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-04-15T03:25:55

I'll post this answer based on my guess that the error is in the line saying

InputFile.Sheets(“AllData”).Activate

In that line you use “AllData” which, due to the use of "smart quotes" instead of normal double-quotation marks, is interpreted by VBA as a variable name and it uses that variable's value as an index to the Sheets collection. However, you have never declared that variable, nor assigned it a value. The Sheets collection accepts either a numeric or string parameter as the index, so you could have said something like “AllData” = 1 or “AllData” = "AllData" and it would have worked.

My guess is that you actually didn't intend to use a variable as the index and you meant to use the statement

InputFile.Sheets("AllData").Activate

There is an immense syntactical difference between the character ", and the smart-quote characters “ and ”. The " character is interpreted by VBA as the start/end of a string literal. The “ and ” characters are interpreted by VBA in the same way as letters such as "a", "b", "c", etc, and therefore can be used in variable names.

Copy range from an active workbook to a closed workbook, MAC OSX, ERROR

1 Answers