Snasui Excel Forum

Code: Select all

Sub saveSheetToCSV()

    Dim myCSVFileName As String
    Dim tempWB As Workbook
    Dim lr As Long

    Application.DisplayAlerts = False
    On Error GoTo err

    myCSVFileName = ThisWorkbook.Path & "\" & "CSV-Exported-File-" & ".csv"

    ThisWorkbook.Sheets("Sheet1").Activate
    ActiveSheet.Copy
    Set tempWB = ActiveWorkbook

    With tempWB
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

Code: Select all

'Other code
With tempWB
    With Sheets("Sheet1")
        .Range("b2", .Range("b" & .Rows.Count).End(xlUp)).NumberFormat = "0"
    End With
    .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close
End With
'Other code