excel vba 2016 Type mismatch 13

[JAWHARRAH]

Good day

I am a newbie and I am desperate.
I keep getting a error type mismatch 13 at runtime but I cannot understand why.
My code is going through the worksheets and I am referencing the codename and I am running into an error.
PLEASE NOTE IT PROCESSES FINE IF I STEP THROUGH THE CODE.
However at runtime I get the type mismatch error.
I have put where it causes the error in **.

my code is

    For Each ws In ActiveWorkbook.Sheets
        Application.DisplayAlerts = False
        swsCode = Mid(ws.CodeName, 6, 2)
'        If swsCode = "" Then MsgBox "It's empty"
'        If swsCode = "" Then swsCode = "1"
'        MsgBox swsCode

**        iwsCode = CInt(swsCode)  **
       
        If iwsCode > 5 Then
            ws.Copy
            xfile = FolderPath & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".csv"
            ActiveWorkbook.SaveAs xfile, FileFormat:=xlCSV
            ActiveWorkbook.Close savechanges:=False
        End If
   
    Next

Please please help me.
Let me know if you need any more info

Jo

 

[mehidy1437]

What is your folder path?
Also why have you kept extra space at ".csv"?

 

[JAWHARRAH]

What is your folder path?
Also why have you kept extra space at ".csv"?

Hi thanks for responding
Folder path = \\fs01\jobs
there is no space at csv?

Like I said it works fine while debugging, it creates the files no issues, just when I run the program.
Thanks
Jo

 

[JAWHARRAH]

Hi thanks for responding
Folder path = \\fs01\jobs
there is no space at csv?

Like I said it works fine while debugging, it creates the files no issues, just when I run the program (via a button) which calls that code it has the error at the line specified above.
Thanks
Jo

 

[Michael M]

Can you post ALL of the code please ??

 

[mehidy1437]

Hope some expert will look into this & help you.

 

[JAWHARRAH]

Can you post ALL of the code please ??

yep
be warned its messy

Option Explicit
Sub CopySelectedSheetsToCSV()
    Dim ws As Worksheet
    Dim iwsCode As Integer
    Dim swsCode As String
    Dim FolderPath As String
    Dim xfile As String
    Application.ScreenUpdating = False
 
    FolderPath = Application.ActiveWorkbook.Path
    On Error GoTo NoWorkbook
    Workbooks(2).Activate

    On Error GoTo 0
    'On Error GoTo CopySelectedSheetsToCSVZ
    'Loop through all worksheets

    For Each ws In ActiveWorkbook.Sheets
        Application.DisplayAlerts = False
        swsCode = Mid(ws.CodeName, 6, 2)
'        If swsCode = "" Then MsgBox "It's empty"
'        If swsCode = "" Then swsCode = "1"
'        MsgBox swsCode
        iwsCode = CInt(swsCode)
      
        If iwsCode > 5 Then
            ws.Copy
            xfile = FolderPath & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".csv"
            ActiveWorkbook.SaveAs xfile, FileFormat:=xlCSV
            ActiveWorkbook.Close savechanges:=False
        End If
  
    Next
    Application.DisplayAlerts = True

ErrorOut:
On Error GoTo 0
    'Clean up the memory usage
    Set ws = Nothing
    Application.ScreenUpdating = True
    Exit Sub
  
CopySelectedSheetsToCSVZ:
    MsgboxEx Prompt:=Err.Number & " - " & Err.Description, _
        Position:=eCentreScreen
    On Error GoTo 0
  
    Resume ErrorOut
  
NoWorkbook:
    MsgboxEx Prompt:="You have not opened a valid workbook for this App!" & _
        DblLineBreak & "Please open your workbook and come back.", _
        Title:="Whoops! No file!!!", _
        Position:=eCentreScreen
      
     Resume ErrorOut
 
End Sub

 

[Michael M]

Try changing this section

For Each ws In ActiveWorkbook.Sheets
Application.DisplayAlerts = False
swsCode = Mid(ws.CodeName, 6, 2)
' If swsCode = "" Then MsgBox "It's empty"
' If swsCode = "" Then swsCode = "1"
' MsgBox swsCode
iwsCode = CInt(swsCode)

Dim swsCode as Integer

For Each ws In Worksheets
Application.DisplayAlerts = False
swsCode = Mid(ws.CodeName, 6, 2) * 1
    If IsNull(swsCode) Then
        MsgBox "It's empty"
        swsCode = 1
    End If
MsgBox swsCode
iwsCode = CInt(swsCode)

 

[JAWHARRAH]

Try changing this section

For Each ws In ActiveWorkbook.Sheets
Application.DisplayAlerts = False
swsCode = Mid(ws.CodeName, 6, 2)
' If swsCode = "" Then MsgBox "It's empty"
' If swsCode = "" Then swsCode = "1"
' MsgBox swsCode
iwsCode = CInt(swsCode)

Dim swsCode as Integer

For Each ws In Worksheets
Application.DisplayAlerts = False
swsCode = Mid(ws.CodeName, 6, 2) * 1
    If IsNull(swsCode) Then
        MsgBox "It's empty"
        swsCode = 1
    End If
MsgBox swsCode
iwsCode = CInt(swsCode)

Thank you for this, much appreciated.
Unfortunately that did not work

It came up with the same error.

for some reason it is will not go passed sheet1 unless i go into debug at the error. Then if I reset, hit F5 it continues and creates the files as per requirement.

 

[Peter_SSs]

When posting code, please use code tags - see these options in the reply window toolbar

I have fixed some of that for you.

With your original code, when you get the error, Debug and hover over the variable name swsCode in the yellow line. What value shows in the pop-up?