Saving column from sheet to CSV file

Question

I would like to take column A from sheet2 and copy it to a new CSV file.

I can copy and save the whole sheet (Sheet2) to a CSV file by using this Sheets("Sheet2").Copy.

When I try to copy and save column A in Sheet2, it copies and saves the whole Workbook.

Sub Save_Sheet2_To_CSV()

Dim MyPath As String
Dim MyFileName As String

MyPath = Range("J10") & "\"
MyFileName = Range("J13")

If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

ActiveWorkbook.Sheets("Sheet2").Columns(1).Copy

ActiveWorkbook.SaveAs FileName:=MyPath & MyFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False

ActiveWorkbook.Close

MsgBox "Sheet2 Export Successful!"

End Sub

Answer 1

Please, test the next updated code:

Sub Save_Sheet2_To_CSV()
 Dim MyPath As String, MyFileName As String, rng As Range

 MyPath = Range("J10").Value
 MyFileName = Range("J13").Value

 If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
 If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

 ActiveWorkbook.Sheets("Sheet2").copy 'it creates a new workbook containing only Sheet 2 shet content
 Set rng = ActiveWorkbook.Sheets(1).UsedRange
 rng.Offset(0, 1).Clear 'keep only the first column

 ActiveWorkbook.saveas filename:=MyPath & MyFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False
 ActiveWorkbook.Close

 MsgBox "Sheet2 Export Successful!"
End Sub