VBA Excel ByRef argument type mismatch when autopopulating new sheets

Question

Following the query here:

VBA Excel autopopulate new sheets based on the cell value in correct order

I would like to automate my worksheet population based on the cell range.

I am using the following code:

 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

I see the error as below:

My range, from where I would like to fetch the sheet names looks as below:

The problem is, that I don't know how to run the defined variant with an existing function. The similar problem here:

ByRef argument type mismatch in Excel VBA

led me to some modifications of my function, which didn't work either. Is there any way to make it running?

You have a sub that accepts one string. You are passing a Variant/Array to it. That won't be fixed by passing that Variant byval. If you want to pass an array, have a different sub that is capable of processing an array. If you don't want to do that, loop over your array yourself and call the sub for each entry. — GSerg
If I change the newName as Variant then I am getting an error: type mismatch for this line: .Item(wsLast.Index + 1).name = "Vetro Area Map " & newName & " 1" — MKR
Similarly newName is an array. What exactly are you trying to do? Are you trying to do something with multiple sheets or a single sheet? — norie
I want to populate multiple sheets based on the values in the range. Basically, I have 2 sheets with different names and I want to assign proper names to them 2. These names come from the range considered. — MKR

VBasic2008 VBasic2008 · Accepted Answer · 2021-03-19T10:05:36

Call a Procedure in a Loop

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

VBA Excel ByRef argument type mismatch when autopopulating new sheets

1 Answers

Call a Procedure in a Loop