2

I have a very similar question as this post: Save individual Excel sheets as CSV

My question differs in that I only need one sheet to be saved This is the answer from that post

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.SaveAs "C:\docs\" & ws.Name & ".csv", xlCSV
Next

This code saves each worksheet to a specific folder and names the file the same as the worksheet. 2 problems I see:

1- The workbook you were currently working on becomes the very last worksheet the code saved. If you want to keep working on your workbook you have to open the original file. It would work better if a new workbook was opened and saved separately from the one that is being worked on.

2- It saves each worksheet. I only need to save one specific worksheet, i.e Sheet2

I found this other code but I am VERY new to VBA and only know how to create a macro, copy & paste code into it. I get error when I run the code.

Sub test()

Application.DisplayAlerts = False

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub

Hopefully I can get pushed in the right direction. Thanks!

Community
  • 1
  • 1
jon
  • 31
  • 1
  • 5

1 Answers1

5
  1. strSourceSheet is the sheet which has the name/number of the sheet which you want to export
  2. strFullname is the filename with path of the csv. As simple as that.

Now see this code

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, _
                      FileFormat:=xlCSV, _
                      CreateBackup:=True
ActiveWorkbook.Close

Let's say I want to copy Sheet2 then I would write it as 

Dim strSourceSheet As String
Dim strFullname As String

'~~> Change the below two lines as per your requirements
strSourceSheet = "Sheet2"
strFullname = "C:\Temp\MyCsv.Csv"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, _
                      FileFormat:=xlCSV, _
                      CreateBackup:=True
ActiveWorkbook.Close

Did you notice how we switch between ThisWorkbook and ActiveWorkbook. When you copy the sheet a new workbook is created and becomes active and hence to close that we use ActiveWorkbook

Hope this helps.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    In your example, strSourceSheet (a string variable) should be set to "Sheet2", not to the worksheet object. – YowE3K Aug 13 '16 at 05:03
  • @YowE3K: Yes, you are right. I had directly typed it without testing it (something which I rarely do). I have amended the post. – Siddharth Rout Aug 13 '16 at 05:05
  • Re `(something which I rarely do)` - you're a better SOer than me - I usually answer without testing, and only notice that it doesn't work when the OP complains of errors. (I'm trying to get better, but it's slow progress.) – YowE3K Aug 13 '16 at 05:06