从活动工作簿到封闭工作簿的复制范围，MAC OSX，错误 [英] Copy range from an active workbook to a closed workbook, MAC OSX, ERROR

问题描述

Peh用户给了我很棒的代码，可以将数据从一个关闭的工作簿复制到一个打开的工作簿，但是现在我想做相反的事情.我想有一个带有公式和值的打开的工作簿，我想将这些作为值复制并粘贴到一个封闭的工作簿中.我想从打开的工作簿的"AllData"选项卡中复制范围 B36:K36 ，以粘贴从一个名为"archive.xlsx"的已关闭工作簿的Sheet1中以 K1 开始的值.

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

当我在活动工作簿中执行此代码时，它成功打开了"archive.xlsx"，并给我一个运行时错误9下标超出范围并且出错，然后我看到它具有selected在活动工作簿中(我要开始粘贴到"archive.xlsx"工作簿中的单元格中)选择 K1 .怎么了?这两个文件都位于我的桌面上的同一文件夹中.

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.

推荐答案

我将根据我的猜测发布该答案，因为我猜错了在行中说

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

InputFile.Sheets("AllData").Activate

在该行中，您使用"AllData" ，由于使用了智能引号"而不是普通的双引号，VBA将其解释为变量名，并使用该变量的值作为 Sheets 集合的索引.但是，您从未声明过该变量，也从未为其分配值. Sheets 集合接受数字或字符串参数作为索引，因此您可能会说类似"AllData" = 1 或"AllData" ="AllData"，它会起作用的.

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

---

字符"与智能引号字符" 和"之间存在巨大的句法差异."字符由VBA解释为字符串文字的开始/结束.VBA用与字母"a"，"b"，"c"等相同的方式来解释" 和" 字符，因此可以使用在变量名中.

---

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.

这篇关于从活动工作簿到封闭工作簿的复制范围，MAC OSX，错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！