自动填充新工作表时，VBA Excel ByRef 参数类型不匹配

Mkr 发表于 Dev

MKR

按照这里的查询：

我想根据单元格范围自动化我的工作表填充。

我正在使用以下代码：

 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

我看到错误如下：

我的范围，我想从哪里获取工作表名称如下：

问题是，我不知道如何使用现有函数运行定义的变体。类似的问题在这里：

Excel VBA 中的 ByRef 参数类型不匹配

导致我对我的功能进行了一些修改，但也不起作用。有没有办法让它运行？

VBasic2008

在循环中调用过程

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。