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

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 wrote:

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

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" wrote:

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 wrote:

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

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.

cardfan3206 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson