Save File In CSV Format VBA

I am using EXCEL 2007 in windows 7.

I have a simple file with 2 worksheets. Sheet1 & Sheet2.

I need to do these things via VBA.

Copy Sheet2!A1:C500 Paste Special Values only.
Delete Sheet2!1:1 row 1 whole row
Delete Sheet1
Ask an Inputbox for the file name
Save As file in CSV format in the below location
C:\Users\RajKum\My Documents\Converted.

Needs to create Converted folder, if not exists.

Appreciate all your help

Raj

Hi Raj Kumar,

Welcome to ExcelFox!

Try

Code:

---

Sub SaveAsCSV()
   
    Dim CSVFileName    As String
    Dim wbkActive      As Workbook
    Dim wbkNew          As Workbook
    Dim i              As Long
    Dim blnFolderExists As Boolean
    Dim FilePath        As String
   
    FilePath = "C:\Users\RajKum\My Documents\Converted\"
   
    With Application
        .ScreenUpdating = 0
        .DisplayAlerts = 0
    End With
   
    Set wbkActive = ThisWorkbook
    Set wbkNew = Workbooks.Add
   
    wbkNew.Worksheets(1).Range("a1:c499").Value = wbkActive.Worksheets(2).Range("a2:c500").Value
   
    CSVFileName = Application.InputBox("Enter the New CSV File Name", Type:=2)
   
    Application.DisplayAlerts = 0
    For i = wbkNew.Worksheets.Count To 2 Step -1
        wbkNew.Worksheets(i).Delete
    Next
   
    blnFolderExists = CBool(Len(Dir(FilePath, vbDirectory)))
   
    If Not blnFolderExists Then
        MkDir FilePath
    End If
   
    If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & Application.PathSeparator
   
    wbkNew.SaveAs Filename:=FilePath & CSVFileName, FileFormat:=6 '"CSV"
    wbkNew.Close
   
    Set wbkNew = Nothing
    Set wbkActive = Nothing
   
    With Application
        .DisplayAlerts = 1
        .ScreenUpdating = 1
    End With

End Sub

---

Thank you, Admin.

Works like a charm...! :thumbsup:

Hi Raj Kumar,

You are welcome !

Glad I could help you. :cheers: