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
– GSerg, Commented Mar 19, 2021 at 9:28
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" — Geographos
– Geographos, Commented Mar 19, 2021 at 9:37
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
– norie, Commented Mar 19, 2021 at 9:41
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. — Geographos
– Geographos, Commented Mar 19, 2021 at 9:45

VBasic2008 · Accepted Answer · 2021-03-19 10:05:36Z

1

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

answered Mar 19, 2021 at 10:05

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geographos Over a year ago

CopyTemplates SheetNames(r, 1) - here debugger shows exactly the same error

VBasic2008 Over a year ago

Try changing the first line of the other procedure: Sub CopyTemplates(ByVal newName As String).

Collectives™ on Stack Overflow

VBA Excel ByRef argument type mismatch when autopopulating new sheets

1 Answer 1

Call a Procedure in a Loop

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Call a Procedure in a Loop

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related