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