Type Mismatch Error - When saving activeworkbook

[beasley101]

Hi guys,

I am having an issue with saving an active workbook, I get "runtime error 13 type mismatch" when I step through the code. The weird thing is, the file actually saves in the location I want it to, but the code stops with that error message after saving.

Dim start As Range
Dim curmth As String
Dim Depot As Integer

curmth = Sheets("Lookups").Range("M29").Value                    'Cell M29 is a formulated cell which retrieves the month which is given in the filename

i = 4

Set start = Sheets("Main List").Range("A" & i)

While start.Value <> ""

Depot = start.Value

If WorksheetFunction.CountIf(Sheets("Claims Summary").Range("A:A"), Depot) > 0 Then
        Sheets("Claims Summary").Copy
        ActiveWorkbook.SaveAs Filename:= _
        "[path]" & Depot & " - Claims Recharges - " & curmth & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

[xladept]

In addition, short file names must not contain the following characters:

plus sign (+)
comma (,)
semicolon (
equals sign (=)
left square bracket ([)
right square bracket (])

from MS Windows Dev Center

[beasley101]

from MS Windows Dev Center

Sorry I should have specified. The [path] was entered to save having to write out the full path of the file location. It does not have square brackets in. It's of the form "C:\abc\def\

[danielexcelvba]

Did you specify anywhere the location to save the file? Or this overwriting the previous file in the same location? Also, which line is causing the error?

[beasley101]

I'm not sure what you mean.

The Filename is the the full extension of the path of where it should be saved. The error comes after pressing F8 on the ActiveWorkbook.SaveAs line.

The weird thing is, the first file actually saves in the location but then I get the Type Mismatch error.

[xladept]

Oh, I thought that if it only happened when you were stepping through that maybe Excel was outsmarting itself as is my wont

[xladept]

I've noticed that when you're stepping through and you have automatic calculation the program jumps to a function
and you often get an error - if this is what's happening, try setting the book to manual calculation and try again -
then if no problem - no problem

[underscoreBadger]

Just an observation, but

Depot = start.Value

will generate a Type 13 run-time error if start.Value is not castable to an integer.

But then that's not the line where the error is being thrown, so that won't be the cause of this particular issue.