Incorrect Function error

C

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
 
D

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?
 
C

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top