vba 引用另一张工作表中的单元格范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19428497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Refereing to a range of cell in another sheet
提问by fmonper1
I am currently trying to use a button to copy all the values inside a range to paste these values inside a range in a different sheet.
我目前正在尝试使用按钮复制范围内的所有值，以将这些值粘贴到不同工作表的范围内。
Excel gives me an error with the second line of this code:
Excel 在这段代码的第二行给了我一个错误：
Sub COPYVALUES()
Range("Base_Copy!F15:Base_Copy!AK46").Select
Selection.Copy
Range("F15:AK46").Select
ActiveSheet.Paste
End Sub
My two sheets are Base_Copywhere the values are, an i want to paste the into Final_Copy
我的两张纸是Base_Copy值所在的位置，我想将其粘贴到Final_Copy
采纳答案by B Hart
There are 3 different methods to reference different sheets. The below was stolen from here: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
有 3 种不同的方法可以引用不同的工作表。以下是从这里窃取的：http: //www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheets Tab Name
表格标签名称
If you have ever recorded a macro in Excel that references a specific sheet in the Workbook you will know that the code will only continue to work if the Sheet name(s) remain the same. For example, code like; Sheets("Budget").Select will no longer work should the Budget Sheet be re-named. This is because the macro recorder generates such code based on the Sheets tab name, the name we see when in Excel. If it makes you feel better, many VBA coders also use the Sheet tab names over two much better ways, as they know no better.
如果您曾经在 Excel 中记录过引用工作簿中特定工作表的宏，您就会知道只有在工作表名称保持不变的情况下，代码才会继续工作。例如，像这样的代码；如果预算表重新命名，Sheets("Budget").Select 将不再起作用。这是因为宏记录器根据“表格”选项卡名称（我们在 Excel 中看到的名称）生成此类代码。如果它让您感觉更好，许多 VBA 编码员也会通过两种更好的方式使用工作表选项卡名称，因为他们知道没有更好的方法。
Index Number
索引号
A sheets Index number is determined by its position in the Workbook. The left most sheet will always have an Index number of 1, the next on the right will be 2 and so on. Excel VBA allows us to specify any Sheet by using it's Index number, but unfortunately this method is not used by Excel when we record a macro. It uses the Sheets Tab name like; Sheets("Budget").Select If this sheet was the third from the left we could use: Sheets(3).Select. This is often a better option than using the sheet tab name, but still has potential problems. By this I mean, the sheets position in the Workbook could change if we add, remove or move sheets.
工作表索引编号由其在工作簿中的位置确定。最左边的工作表的索引号始终为 1，右边的下一个为 2，依此类推。Excel VBA 允许我们通过使用它的索引号来指定任何工作表，但不幸的是，当我们记录宏时，Excel 不使用这种方法。它使用 Sheets Tab 名称，例如；Sheets("Budget").Select 如果此工作表是左数第三个，我们可以使用：Sheets(3).Select。这通常比使用工作表标签名称更好，但仍然存在潜在问题。我的意思是，如果我们添加、删除或移动工作表，工作簿中的工作表位置可能会发生变化。
Sheets Code Name
表格代码名称
This is the method used by savvy VBA coders. Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets CodeName can only be seen by going into the Visual Basic Editor (Tools>Macro>Visual Basic Editor Alt+F11) and then displaying the Project Explorer (View>Project Explorer Ctl+R)
这是精明的 VBA 编码员使用的方法。工作簿中的每个工作表都有一个唯一的代码名称，即使在移动、重命名或添加其他工作表时也不会更改。每个工作表 CodeName 只能通过进入 Visual Basic 编辑器（工具>宏>Visual Basic 编辑器 Alt+F11）然后显示项目资源管理器（视图>项目资源管理器 Ctl+R）来查看
In the screen shot above, the CodeName for the sheet with a tab name of Budget is Sheet3. A sheets CodeName is always the name not inside the parenthesis when looking in the Project Explorer. We can reference this sheet with VBA code in the Workbook by using: Sheet3.Select as apposed to Sheets("Budget").Select or Sheets(3).Select
在上面的屏幕截图中，标签名称为 Budget 的工作表的 CodeName 是 Sheet3。在 Project Explorer 中查看时，表 CodeName 始终是不在括号内的名称。我们可以使用工作簿中的 VBA 代码引用此工作表： Sheet3.Select as apposed to Sheets("Budget").Select 或 Sheets(3).Select
If your Workbook is already full of VBA code, recorded or written, that does not use the CodeName you can change it on a Project level (all code in all Modules in the Workbook) by going to Edit>Replace while in the VBE (Visual Basic Editor). One Draw back
如果您的工作簿已经充满 VBA 代码，记录或编写，不使用 CodeName，您可以在项目级别（工作簿中所有模块中的所有代码）更改它，方法是在 VBE（可视化基本编辑器）。一退
The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides.
唯一不能使用工作表 CodeName 的情况是当您将不同工作簿中的工作表引用到代码所在的工作簿时。
With the above information your code now becomes:
有了上述信息，您的代码现在变为：
Sub COPYVALUES()
Sheets("Base_Copy").Range("F15:AK46").Copy
' OR Sheets(2).Range("F15:AK46").Select
' OR Sheet2.Range("F15:AK46").Select
Sheets("Final_Copy").Range("F15:AK46").Paste
Application.CutCopyMode = False
End Sub
Now on a side note: You don't have to use copy/paste to move data...
现在附带说明：您不必使用复制/粘贴来移动数据...
Try the below single line of code:
试试下面的单行代码：
Sub COPYVALUES()
Sheets("Final_Copy").Range("F15:AK46").Value = Sheets("Base_Copy").Range("F15:AK46").Value
End Sub
回答by Joe Laviano
Use:
用：
Sheets("Base_Copy").Range("F15:AK46")
Your idea is more of the in-cell formula type, which is valid in the sheet, but VBA keeps it a little simpler.
您的想法更多是单元格公式类型，它在工作表中有效，但 VBA 使它更简单一些。
回答by Harrison
It's as simple as this. You must specify your Worksheetin the Worksheetsobject prior to the Range.
就这么简单。您必须指定Worksheet在Worksheets对象之前Range。
Sub COPYVALUES()
Worksheets("Final_Copy").Range("F15:AK46").Value = Worksheets("Base_Copy").Range("F15:AK46").Value
End Sub
See the example in Range Object (Excel)
请参阅范围对象 (Excel) 中的示例
For something like this you may want to record the macro to see the syntax of how it is written, then modify it to your needs.
对于这样的事情，您可能希望录制宏以查看其编写方式的语法，然后根据需要对其进行修改。
