vba 使用变量作为工作表名称

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/16061599/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 15:26:03  来源:igfitidea点击:

Using a variable as a sheet name

excelvbaexcel-vba

提问by subtilty

I am getting a RunTime 13 error when trying to use a variable for a sheetname as per below:

尝试将变量用于工作表名称时,出现 RunTime 13 错误,如下所示:

Sub inputdata()

Set asheet1 = ThisWorkbook.Worksheets("input").Range("D12")
Set rangeDate = ThisWorkbook.Worksheets("input").Range("inputdate")

Range("F12:M12").Copy
Sheets(asheet1).Select

It is erroring on the line Sheets(asheet1).Select

它在行 Sheets(asheet1).Select 上出错

Any help would be great thanks!

任何帮助都会非常感谢!

回答by Ateszki

The asheet1 is not a string, you are asigning a range object to it . You should declare asheet1 as string and the change this line to

asheet1 不是字符串,您正在为其分配一个范围对象。您应该将 asheet1 声明为字符串并将此行更改为

Dim asheet1 as string
asheet1 = ThisWorkbook.Worksheets("input").Range("D12").Value

That should make it work!

这应该使它工作!

Edit

编辑

removed the Set keyword from the string var.

从字符串 var 中删除了 Set 关键字。

回答by shahkalpesh

Option Explicit

Sub inputdata()
dim inputSheet as WorkSheet
dim aCellOnInputSheet as Range
dim inputDateCell as Range
dim userSheetName as String


Set inputSheet = ThisWorkbook.Worksheets("input") 

Set aCellOnInputSheet = inputSheet.Range("D12")
userSheetName = aCellOnInputSheet.Value
Set inputDateCell = inputSheet.Range("inputdate")

Range("F12:M12").Copy
Sheets(userSheetName).Select

End Sub

EDIT: A couple of points
1) Option Explicitis a must.
2) Define variables and name it appropriately. i.e. define variable which refers to a cell/Range with the name range instead of aSheet- it confuses the reader

编辑:几点
1)Option Explicit是必须的。
2)定义变量并适当命名。即定义引用单元格/范围的变量,而不是名称范围aSheet- 它使读者感到困惑