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

Board Regular
Joined
May 10, 2022
Messages
78
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:cry::: because the actual sheet comes from a file in xlsm format but in your code there is no warning (y) :

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.
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Your 2nd code looks like it should work with these two changes:
VBA Code:
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.
 
Upvote 0
Solution
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 ?
 

Attachments

  • InputBox.PNG
    InputBox.PNG
    7.6 KB · Views: 7
Upvote 0
Try adding this line straight after you InputBox
VBA Code:
 If StrPtr(Namefile) = 0  then Exit Sub
 
Upvote 0
Unfortunately, it's not working, it still create a new workbook with the data of the sheet.
 
Upvote 0
Does your code now look like this ?
VBA Code:
Namefile = InputBox("Please give a name to your File")
If StrPtr(Namefile) = 0 Then Exit Sub
SaveName = Namefile
 
Upvote 0
Sorry my bad, I was running the wrong macro. Now it's working perfectly thank you so much . 🙏
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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