Run-time error '13': Type mismatch

[celias]

Hello, brilliant people!
I am trying to adjust a file with macros that was previously built and I need some help, if possible. After making a few changes, I get the error mentioned above when running a macro.
When debugging, it points the Line 44: "SheetName = SheetNames(i, 1)"


The code is below.
Any help is very much appreciated. Thank you in advance for your time.

Private Sub CommandButton1_Click()
  Call MonthEndMachine
End Sub

Sub MonthEndMachine()
Dim NofSheets As Integer
Dim SheetNames As Variant
Dim SheetName As String
Dim FileNames As Variant
Dim FileName As String
Dim StaffHeaderRg As Range
Dim StaffFormatRg As Range
Dim Path As String
Dim cell As Range
Dim LastCell As Range
Dim Target As Range
Dim i As Integer
Dim ComboRow As Long
Dim LastRow As Long
Dim NameRange As String
Dim LastRowCombo As Integer
Dim ComboRange As String
Dim LastRowPCSGL_Only As Integer
Dim LastRowColA As Integer
Dim DataRange As String


Application.Calculation = xlCalculationManual 'stop sheets from calculating

Path = "I:\PCSGLStaff\Celia\Candema" ' where the staff folders are located
NofSheets = ActiveWorkbook.Names("LastRow_Staff").RefersToRange.Value 'Hidden sheet Info watches how many names are listed
SheetNames = ActiveWorkbook.Names("StaffList").RefersToRange.Value ' StaffList is a dynamic range that looks at the staff names
FileNames = ActiveWorkbook.Names("FileList").RefersToRange.Value ' FileList is a dynamic range that contains the auto-generated file names that we need to go get
Set StaffHeaderRg = ActiveWorkbook.Names("StaffHeader").RefersToRange ' Where the pretty header for the staff pages is stored
Set StaffFormatRg = ActiveWorkbook.Names("StaffFormat").RefersToRange ' Where the format line for the staff pages is stored


'Clear the Combo sheet (start with a clean slate)
Worksheets("Combo").Cells.Clear

i = 1
ComboRow = 1 ' start pasting at row one
For i = 1 To NofSheets
    SheetName = SheetNames(i, 1)
    FileName = FileNames(i, 1)
    
    ' Go get all the data

    With Worksheets(SheetName)
        Sheets(SheetName).Activate 'just for show (we really don't need to activate the sheets to work with them, but it's nice for people to see the sheets clicking by as a way of measuring progress)
        .Range("A1").Select
        .Cells.Clear ' clear entire sheet (just in case this is not the first run)
        
        ' Go get the data from the sheet called "CalcData" in workbook called [FileName] in directory called [SheetName]
        GetDataFromClosedWorkbook Path & "\" & SheetName & "\" & FileName, "CalcData", Worksheets(SheetName).Range("A1"), True
        
        ' Find the last row (no all user's sheets are the same size now)
        LastRowColA = .Range("A65536").End(xlUp).Row
        ' If the sheet is blank don't copy the header row over to Combo
        If LastRowColA < 2 Then
            LastRowColA = 2
            End If
        
        DataRange = "I1:I" & LastRowColA
        ' Fill the last column I with the person's name
        .Range(DataRange) = SheetName
        
        ' Now just copy the actual data rows (not the blanks) to Combo
        DataRange = "A2:A" & LastRowColA
        Set Target = .Range(DataRange)
        For Each cell In Target
            Select Case cell.Value
                Case "" ' if the A column value is blank, then we won't copy the row
                    'do nothing
                Case Else
                    cell.EntireRow.Copy ThisWorkbook.Worksheets("Combo").Rows(ComboRow)
                    ComboRow = ComboRow + 1
            End Select
        Next cell

        ' Before we leave this sheet, let's make it pretty
            .Columns(9).EntireColumn.Delete ' get rid of name column
            .Rows(1).EntireRow.Delete ' get rid of header row
        ' Sort the staff sheet
            .Range("A:H").Sort Key1:=.Range("A:A"), Order1:=xlAscending, Key2:=.Range("B:B"), Order2:=xlAscending, Key3:=.Range("D:D"), Order3:=xlAscending, Orientation:=xlTopToBottom
        ' Copy the nice format
            DataRange = "A1:H" & LastRowColA
            Set Target = .Range(DataRange)
            StaffFormatRg.Copy
            Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' Put a nice header on top
            .Range("A1:A3").EntireRow.Insert
            StaffHeaderRg.Copy .Range("A1:H3")
            .Range("A1") = SheetName
            .Range("A1").Select
    End With
        
Next i

    Application.Calculate 'update all values
    
    'Some final Combo fix ups
    LastRowCombo = ActiveWorkbook.Names("LastRow_Combo").RefersToRange.Value ' how many rows Combo sheet has

    With Worksheets("Combo")
        'Sort combo
        .Range("A:I").Sort Key1:=Sheets("Combo").Range("B:B"), Order1:=xlAscending, Key2:=Sheets("Combo").Range("D:D"), Order2:=xlAscending, Orientation:=xlTopToBottom
        ' Put in mistake counter
        .Range("j1:j1").Formula = "=COUNTIF(PCSClients,A1)"
        ComboRange = "J1:J" & LastRowCombo
        .Range(ComboRange).FillDown
    End With

    'Some final PCSGL_Only fix ups
    LastRowPCSGL_Only = ActiveWorkbook.Names("LastRow_PCSGL_Only").RefersToRange.Value + 3

    With Worksheets("PCSGL_Only")
        PCSGLRange = "A5:J" & LastRowPCSGL_Only
        .Range(PCSGLRange).ClearContents
        PCSGLRange = "A4:J" & LastRowPCSGL_Only
        .Range(PCSGLRange).FillDown
        Application.Calculate 'update all values
        PCSGLRange = "A5:J" & LastRowPCSGL_Only
        .Range(PCSGLRange).Copy
        .Range(PCSGLRange).PasteSpecial Paste:=xlValues
        '.Range("A2").Select
    End With


    Application.Calculation = xlCalculationAutomatic

    Sheets("Done").Activate
    
End Sub

 

[marcelocbd]

You get a Type Mismatch error when you try to atribute a value to a variable of different types.

In here

SheetName = SheetNames(i, 1)

You define the variable SheetName, that it is a String variable, to be the value of the cell A1 of the sheet StaffList.

Probably the cell is a number, not a string. It loops through column A x times (x being the number of tabs you have). So, all cells from A1 to Ax have to be strings.

If you want to save the cell values to SheetName variable as String no matter what, try changing the line for:

SheetName = CStr(SheetNames(i, 1) )

 

[celias]

Unfortunately it didn't work... Any other ideas of what it might be causing this?