自动填充新工作表时，VBA Excel ByRef参数类型不匹配
自动填充新工作表时，VBA Excel ByRef参数类型不匹配
提问于 2021-03-19 17:19:03
下面的查询如下：
VBA Excel autopopulate new sheets based on the cell value in correct order
我想根据单元格范围自动填充我的工作表。
我使用了以下代码：
Sub Sheetaddingnamefinal()
Dim SheetNames As Variant
Dim lr As Long
lr = ThisWorkbook.Sheets("Frontsheet").Cells(Rows.Count, 4).End(xlUp).Row
SheetNames = ThisWorkbook.Worksheets("Frontsheet").Range("D123:D" & lr)
CopyTemplates SheetNames
End Sub
Sub CopyTemplates(newName As String)
'these are the template worksheets
Const WS_A As String = "Vetro Area Map 1"
Const WS_B As String = "Area Map Op 1"
Dim wsLast As Worksheet, i As Long, ws As Worksheet
'find the last worksheet which looks like one of our templates
' (or a copy of one of the templates)
For i = 1 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
If ws.name Like "Vetro Area*" Or ws.name Like "Area Map*" Then
Set wsLast = was
End If
Next i
'copy the templates after the "last" copy and rename
With ThisWorkbook.Worksheets
.Item(Array(WS_A, WS_B)).Copy after:=wsLast
.Item(wsLast.Index + 1).name = "Vetro Area Map " & newName & " 1"
.Item(wsLast.Index + 2).name = "Area Map Op " & newName & " 1"
End With
End Sub我看到的错误如下：
我希望从中获取工作表名称的范围如下所示：
问题是，我不知道如何使用现有函数运行已定义的变体。这里也有类似的问题：
ByRef argument type mismatch in Excel VBA
导致我对我的函数进行了一些修改，但也不起作用。有没有办法让它运行起来？
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-03-19 18:05:37
在循环中调用过程
Option Explicit
Sub Sheetaddingnamefinal()
Dim SheetNames As Variant
Dim lr As Long
With ThisWorkbook.Sheets("Frontsheet")
lr = .Cells(.Rows.Count, "D").End(xlUp).Row
SheetNames = .Range("D123:D" & lr).Value
End With
Dim r As Long
For r = 1 To UBound(SheetNames, 1)
CopyTemplates SheetNames(r, 1)
Next r
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66705455
复制相关文章
相似问题
腾讯云开发者
