Compile Error: ByRef argument type mismatch creating new sheets

Question

This code is defining a function that checks if a Sheet exists and returns a boolean value. The Sub is attempting to activate a workbook. It then checks a sheet (DataEntry) and iterates through the rows and checks if the teamname is already a sheet and if it isn't create a new sheet of that name and paste the data into that sheet. If the sheet does exist then it copies it into the existing sheet. Then it adds 1 to the match number so that when it goes to paste again as that determines what row it pastes the data in. But when I attempt to run it via a button on the sheet it gives me the byref argument type mismatch error.

Any thoughts on what might be wrong?

Function shtexists(shtname As Worksheet) As Boolean
    Dim sht As Worksheet
    Set sht = Sheets(shtname)
    On Error GoTo 0
    SheetExists = Not sht Is Nothing
End Function

Sub AddData()
    Workbooks("ScoutingDatabseMaster.xlsm").Activate
    Dim teamname As String
    Dim countery As Integer
    Dim matchnumber As Integer
    matchnumber = 1
    countery = 4
    teamname = Range("B4", "B" & countery)
    For countery = 4 To 9
        If shtexists(teamname) Then
            Worksheets("DataEntry").Range("C" & countery, "M" & countery).Copy
            Worksheets(teamname).Range("A" & matchnumber).Paste
        If teamname = "" Then
            MsgBox ("You forgot to Enter a Team Number!")
        Else
            Sheets.Add.Name = teamname
        End If
        Next countery
    matchnnumber = matchnumber + 1
    countery = 4
End Sub

Shai Rado · Accepted Answer · 2017-03-11 08:50:50Z

3

You are calling If shtexists(teamname) Then when teamname is a String, while Function shtexists(shtname As Worksheet) As Boolean, shtname As Worksheet, this will raise a run-time error. shtname should be String as well.

Change your Function declaration to:

Function shtexists(shtname As String) As Boolean
    Dim sht As Worksheet
    Set sht = Sheets(shtname)
    On Error GoTo 0
    shtexists = Not sht Is Nothing
End Function

edited Mar 11, 2017 at 8:50

answered Mar 11, 2017 at 5:50

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

1 Comment

PSotor Over a year ago

Or the other way - call it like this shtexists(Worksheets("teamname"))

Collectives™ on Stack Overflow

Compile Error: ByRef argument type mismatch creating new sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related