Incorrect Function error

[cardfan3206]

Hi All,

I wrote the following code to hide certain columns and rows of data, then
rename the file and save it to a directory. The code runs completely but I
am getting an incorrect function error when it is finished. Any ideas what I
can do to solve the problem?

Sub WorkbookClose()
'
' WorkbookClose Macro
' Formats file for printing and saves to directory
'
'

' Hides Rows that are empty
'
Dim rangeToTest As Range
Dim anyCell As Object

Set rangeToTest = Range("K13:K22")
For Each anyCell In rangeToTest
If IsEmpty(anyCell) Then
anyCell.EntireRow.Hidden = True
End If
Next

'Hides columns containing raw/unadjusted data

Columns("A:A").Select
Selection.EntireColumn.Hidden = True
Columns("J:U").Select
Selection.EntireColumn.Hidden = True
ChDir "G:\Compensation\Market Analysis Files\"

mySerial = ""
myPath = "G:\Compensation\Market Analysis Files\"
myFile = Sheets("Market Detail").Range("C9") & " - " & Sheets("Market
Detail").Range("C8") & " - " & Format(Date, "MM-DD-YYYY")
myExt = ".xlsm"

' create output using sequence 1 to n if file already exists
If Len(Dir(myPath & myFile & mySerial & myExt)) > 0 Then

Do While Len(Dir(myPath & myFile & mySerial & myExt)) > 0
mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")"
Loop

End If

ThisWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt

Range("H12").Select
ThisWorkbook.Close
End Sub

 

[Dave Peterson]

If the line that's getting the error is the .SaveAs, try adding the FileFormat:=
parameter to your code--and use the one that's associated with .xlsm files.
(The extension has to match the fileformat.)

If it's not that line, what line is it?

 

[cardfan3206]

Thanks Dave. I tried to change the code to FileFormat instead of the myExt
code I have now and it didn't change anything. Unfortunately I can't seem to
isolate where the problem is because I don't get any debug errors that take
me into the code. The code simply executes and then as the file is closing,
I get the incorrect function error. Any other ideas?

 

[Dave Peterson]

Include both the extension and the fileformat in that line (if you didn't try
that already).

You may want to share what you tried if that doesn't work.