Macro Error

[awappel]

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

 

[jmthompson]

On what line is the error?

 

[awappel]

Sheets("Listing Template").Copy After:=Workbooks(sNewWorkbookName).Sheets(3)

The above.

 

[jmthompson]

My guess is that your workbook only has 2 sheets, this line dictates to copy your sheet after sheet 3. Can't do that if there's no sheet 3.

 

[shg]

Another way, perhaps:

Sub Format_And_SaveAsCSV()
    Dim wkb         As Workbook
    Dim sFile       As String
    Dim nRow        As Long
 
    Call FormatOnly

    Set wkb = ActiveWorkbook
    sFile = Replace(wkb.FullName, ".xls", ".csv")
    wkb.Worksheets("Listing Template").Copy
 
    With ActiveWorkbook
        nRow = Cells(Rows.Count, "A").End(xlUp)
        Rows(nRow + 1).Resize(Rows.Count - nRow).Delete
        Columns("AX:IV").Delete
        .SaveAs Filename:=sFile, _
                FileFormat:=xlCSV
        .Close SaveChanges:=False
    End With
 
    MsgBox "The CSV has been formatted, saved, and is ready to upload: " _
           & vbLf & vbLf & sFile _
           & vbLf & vbLf & "Record count: " & nRow
End Sub

 

[awappel]

Perfect Thanks again for all your help

 

[shg]

If that was addressed to me, you're welcome.