I keep getting a run time error 9 out of range when I runt he below macro, I can't seem to figure out what is wrong with it, any ideas?
Sub Format_And_SaveAsCSV()
Dim csvFileName As String, nRowCount As Long
Call FormatOnly
Sheets("Listing Template").Select
sFileName = Replace(ActiveWorkbook.FullName, ".xls", ".csv")
sWindowName = ActiveWorkbook.Name
Workbooks.Add
sNewWorkbookName = ActiveWorkbook.Name
Windows(sWindowName).Activate
Sheets("Listing Template").Select
Sheets("Listing Template").Copy After:=Workbooks(sNewWorkbookName).Sheets(3)
' remove trailing rows
Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
sRange = Selection.Row & ":65536"
Rows(sRange).Select
Selection.Delete Shift:=xlUp
nRowCount = Selection.Row - 2
' remove trailing columns
Columns("AX:IV").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
sFileName, FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
MsgBox "The CSV has been formatted, saved, and is ready to upload: " _
& vbCrLf & vbCrLf & sFileName _
& vbCrLf & vbCrLf & "Record count: " & nRowCount
End Sub
Sub Format_And_SaveAsCSV()
Dim csvFileName As String, nRowCount As Long
Call FormatOnly
Sheets("Listing Template").Select
sFileName = Replace(ActiveWorkbook.FullName, ".xls", ".csv")
sWindowName = ActiveWorkbook.Name
Workbooks.Add
sNewWorkbookName = ActiveWorkbook.Name
Windows(sWindowName).Activate
Sheets("Listing Template").Select
Sheets("Listing Template").Copy After:=Workbooks(sNewWorkbookName).Sheets(3)
' remove trailing rows
Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
sRange = Selection.Row & ":65536"
Rows(sRange).Select
Selection.Delete Shift:=xlUp
nRowCount = Selection.Row - 2
' remove trailing columns
Columns("AX:IV").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
sFileName, FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
MsgBox "The CSV has been formatted, saved, and is ready to upload: " _
& vbCrLf & vbCrLf & sFileName _
& vbCrLf & vbCrLf & "Record count: " & nRowCount
End Sub