宏或 VBA 中的动态工作簿和工作表名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22502808/
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
Dynamic Workbook and Worksheet name in Macro or VBA
提问by DA69
I have this code that works: (am currently using Excel 2003)
我有这个有效的代码：（我目前使用的是 Excel 2003）
Sub test_copy1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb1 = Workbooks("Workbook2.xls")
Set wb2 = ThisWorkbook
Set ws1 = wb1.Sheets("Run 1")
Set ws2 = wb2.Sheets("Sheet3")
ws2.Range("A1:h30") = ws1.Range("A177:H206").Value
End Sub
In the current Workbook "Sheet 3" Cell J1 = Workbook2.xls Cell J2 = Run 1
在当前工作簿“工作表 3”单元格 J1 = Workbook2.xls 单元格 J2 = 运行 1
How can I feed the information from the current Workbook "Sheet 3" into the macro from the cells so that it may be changed as needed and still work?
如何将当前工作簿“工作表 3”中的信息从单元格输入到宏中，以便可以根据需要进行更改并仍然有效？
What I mean is to have 2 cells that can change in a sheet, either by drop down or manually filled in on the current workbook sheet "sheet 3". Cell: J1 would equal a file name (Workbook2.xls) and J2 would equal a sheet name (Run 1).
我的意思是有 2 个可以在工作表中更改的单元格，可以通过下拉或手动填写在当前工作簿工作表“工作表 3”上。单元格：J1 等于文件名 (Workbook2.xls)，J2 等于工作表名称（运行 1）。
The user would be able to change the file name and sheet name when they needed and then hit a button to process the copy and paste.
用户可以在需要时更改文件名和工作表名称，然后点击按钮来处理复制和粘贴。
I would like the code to take the information from the cells and put them into the code instead of "hard" coding the file name of the workbook and sheet.
我希望代码从单元格中获取信息并将它们放入代码中，而不是“硬”编码工作簿和工作表的文件名。
I hope that makes more sense
我希望这更有意义
Thank you for any help.
感谢您的任何帮助。
~DA
~DA
-----UPDATE----
- - -更新 - -
I have tried this code but it keeps crashing on the first Set Wb1=
我已经尝试过这段代码，但它在第一个 Set Wb1= 上一直崩溃
Sub test_copy1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb1 = Workbooks(ws2.Range("J1"))
Set wb2 = ThisWorkbook
Set ws1 = wb1.Sheets(ws2.Range("J2"))
Set ws2 = wb2.Sheets("Sheet3")
ws2.Range("A1:h30") = ws1.Range("A177:H206").Value
End Sub
------Update -------
- - - 更新 - - - -
I got it to work using you all examples and adding .Value after the range :)
我使用你所有的例子让它工作，并在范围后添加 .Value :)
Sub test_copy1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb2 = ThisWorkbook
Set ws2 = wb2.Sheets("Sheet3")
Set wb1 = Workbooks(ws2.Range("J1").Value)
Set ws1 = wb1.Sheets(ws2.Range("J2").Value)
ws2.Range("A1:h30") = ws1.Range("A177:H206").Value
End Sub
Thank you, thank you for all your help!!
谢谢，谢谢大家的帮助！！
回答by Dmitry Pavliv
Try this one:
试试这个：
Sub test_copy1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb2 = ThisWorkbook
Set ws2 = wb2.Sheets("Sheet3")
Set wb1 = Workbooks(ws2.Range("J1").Value)
Set ws1 = wb1.Sheets(ws2.Range("J2").Value)
ws2.Range("A1:H30").Value = ws1.Range("A177:H206").Value
End Sub
