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

 

[Peter_SSs]

I followed all those steps and the 5 csv files got created with no error message.

 

[JAWHARRAH]

Ok, thank you Peter

 

[JAWHARRAH]

Hi Peter
I still get the same error. the codenames are empty but the sheet names are displayed as they should be.
If I run it to the point of the error and save the file, and resume by reopening everything, it still has the same error.
If I go into debug and end the subroutine and rerun it using F5, it works. I don't understand why I am getting a type mismatch error.
I did a repair thinking it might be my Office, but I had the error at home also.
Do you have any other suggestions?
Is there a way the code could simulate - ending at the error, go into debug, and run - without having to hit debug etc... ???
Regards
Jo

 

[Peter_SSs]

Not being able to reproduce the error means that I cannot really investigate alternatives I'm afraid. Not sure what to suggest.

 

[JAWHARRAH]

I have redone the file, hopefully this will produce the error.
Open MATERIALS PLAN, (userform will open)
Open 111222_2020_09_07.
Go to the export to CSV page on MATERIALS PLAN userform.
On MATERIALS PLAN select the export to csv button.
It will ask if you are ready to export.
Once you say ok, it goes through the code you gave me, (showing msgboxes with no sheet name).
It should be exporting 4 csv files.
I have tried this a couple of times. I did not go into debug with this file. I just kept closing and reopening it and it gives the error.
Hopefully you can help.
Thanks
Jo
Excel

 

[Peter_SSs]

OK, I am now able to reproduce that error.
I'm still not sure why, but there seems to be an issue with the particular sheet 'Initial Data' (hidden) though not with the other hidden sheets as far as I can tell so far.
Since you are not processing any of the hidden sheets anyway, try these changes. So far (not greatly tested though) I have not had a recurrence of the error with this change in place.

For Each ws In ActiveWorkbook.Sheets
    If ws.Visible Then
      Application.DisplayAlerts = False
      swsCode = Mid(ws.CodeName, 6, 2)
      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
    End If
Next

 

[JAWHARRAH]

Hi Peter
First of all I want to thank you very much for all of your time and effort, you have put into this.
Unfortunately that does not work.
It runs through the program, however no files are generated, then when I try and run it again from that point, it comes up with the type mismatch.

I have even tried to run this without the worksheets hidden, but it still does not work.

I don't know what I can do. I don't know why this went wrong, however I have learned a lot from you.
Much appreciated.
Jo

 

[Peter_SSs]

I can't reproduce that behaviour either & it does not make sense. The only other thing that comes to mind is that there could be some corruption in one or both the workbooks.
It might be worth trying to create a new ones from scratch (and copy your codes over) and see what happens then.

 

[JAWHARRAH]

Hi Peter
That was a thought some time ago, so I did create a new workbook. It was definitely corrupted though because there were sheets when there were no sheets. see old version below
Jo