Copy all the data from one specific sheet, paste and save that on a new workbook which as to be save in csv on a specific folder path - VBA

[Gwhaou]

Hello, I'm new to vba, i saw a post when i was researching to: how to copy all the details from one sheet, copy this and save it in csv format on a specific folder.
Then luckily I saw the code @Georgiboy posted and tested it:

Sub Print_Test()
Dim FSO As Object
Dim TextFile As Object
Dim rCell As Range
Dim CSVFilePath As String
Dim ws As Worksheet

CSVFilePath = "C:\Users\jbloggs\Desktop\test\"
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 10) <> "Sheet name" Then
Set TextFile = FSO.CreateTextFile(CSVFilePath & ws.Name & ".csv")
TextFile.Close
Open CSVFilePath & ws.Name & ".csv" For Output As #1
For Each rCell In ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row).Cells
Print #1, rCell.Value
Next rCell
Close #1
End If
Next ws
End Sub

I need some help to modify this code to copy all the cells form, one particular sheet and not all the sheets without using the FOR condition.
Currently, I'm using this code which is not fully practical because when i'm trying to open the saved sheet a warning message appears:: because the actual sheet comes from a file in xlsm format but in your code there is no warning :

My code, I used a simple code :

Sub SaveCSV()

Dim Namefile As Variant
Dim SaveName As
On Error GoTo Canceled

Namefile= InputBox("Please give a name to your File")
SaveName =Namefile

Sheets("Prg_1").Copy 'The sheet which as to be copy and save
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:="C:\Users\JD\Desktop\" & SaveName & ".csv" 'Saving the sheet on the desktop with the name type by the user
.Close savechanges:=True
End With
Application.DisplayAlerts = True

End Sub

The first code works perfectly but take data only from A2 to the last cell and print that in the cell "A" but i want all the data from one particular sheet which is Prg_1, I would appreciate your help to solve my problem.
By the way sorry for my english is not that good.

 

[John_w]

Your 2nd code looks like it should work with these two changes:

Dim SaveName As String

.SaveAs Filename:="C:\Users\JD\Desktop\" & SaveName & ".csv", FileFormat:=xlCSV

I would delete the On Error GoTo Canceled line because you don't have a label named Canceled.

 

[Gwhaou]

Thanks it works , but i have another problem, this time is with the input box, when I run the macro SaveCSV() normally by typing the name of the file, it's correctly saved in the desktop but when I use to click on the Cancel or Close button (InputBox), the macro continue the programme and open another workbook with the data of the sheet.
Do you know how to stop the programme when I click on the Cancel or Close button ?

 

[Alex Blakenburg]

Try adding this line straight after you InputBox

 If StrPtr(Namefile) = 0  then Exit Sub

 

[Gwhaou]

Unfortunately, it's not working, it still create a new workbook with the data of the sheet.

 

[Alex Blakenburg]

Does your code now look like this ?

Namefile = InputBox("Please give a name to your File")
If StrPtr(Namefile) = 0 Then Exit Sub
SaveName = Namefile

 

[Gwhaou]

Sorry my bad, I was running the wrong macro. Now it's working perfectly thank you so much .

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.