CODEWITHSUNDEEP
vbaexcel
DINESHKUMAR PALANISAMY
DINESHKUMAR PALANISAMY

Reputation: 159

Saving Sheet as .CSV file

I have to copy the data and paste it in new sheet and change the sheet name as per user requirement and save it with the same name in desired location.I have written the code and executed but I cannot find the file in specified location.Please help me on this.

Sub saveascsv()

Dim Rng As Range
Dim filenam As Variant
Dim saveasfile As Variant

filenam = InputBox("Enter name of the file to be saved")

Set Rng = Range("E1:H" & Range("H" & Rows.Count).End(xlUp).Row)
Rng.Select
Selection.Copy

ActiveWorkbook.Sheets.Add after:=Worksheets("Part_Number")
ActiveSheet.Name = filenam
ActiveSheet.Paste

ActiveSheet.Columns("A:D").AutoFit

Application.CutCopyMode = False

saveasfile = Application.GetSaveAsFilename(InitialFileName:=filenam, 
FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As")

If saveasfile <> "False" Then
   MsgBox "saveas " & filenam
End If

End Sub

Upvotes: 1

Views: 232

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You're not saving anything, just getting the file's name to save and displaying a message box.

If saveasfile <> "False" Then
    ActiveSheet.move                     ' <-- Add this line
    ActiveSheet.SaveAs saveasfile, xlCSV ' <-- Add this line
    MsgBox "saved as " & saveasfile
End If

Upvotes: 2

Related Questions