Macro Error

awappel

New Member
Joined
Mar 15, 2011
Messages
14
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
Another way, perhaps:

Code:
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
 
Upvote 0
If that was addressed to me, you're welcome.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top