VBA SaveCopyAs with File Format question

[mriveraCP]

After reading threads, I have realized you cannot change the File Format using the SaveCopyAs Filename syntax. How do I get around this? I have created a command button that is creating a folder on my computer, saving my current macro enabled file as a .csv and everything is working...however, it changes the current workbook file name instead of creating a copy and it changes the tab name in the workbook to the new SaveAs file name. Any tips are much appreciated?

Here is my current code below:

Sub SaveCSV()
'
' SaveCSV Macro
'
ChDir "C:\VSA\ForecastComments"
ActiveWorkbook.SaveAs Filename:= _
"C:\VSA\ForecastComments\2017 FC Comments-" & Worksheets("Summary").Cells(1, 2).Text & " " & Worksheets("Summary").Cells(2, 2).Text & ".csv", FileFormat:= _
xlCSV, CreateBackup:=False
End Sub

 

[ZVI]

Hi and welcome to MrExcel!
Try this code:

Sub SaveAsCsv()
 
  Dim FName As String
 
  FName = "C:\VSA\ForecastComments\2017 FC Comments-" & Worksheets("Summary").Cells(1, 2).Text & " " & Worksheets("Summary").Cells(2, 2).Text & ".csv"
  
  ' Stop screen refreshing
  Application.ScreenUpdating = False
   
  ' Create a copy of active sheet in the new workbook
  ActiveSheet.Copy
 
  ' Save the new workbook as CSV and close it
  With ActiveWorkbook
    .SaveAs Filename:=FName, FileFormat:=xlCSV, CreateBackup:=False
    .Close False
  End With
 
  ' Return back to this workbook
  ThisWorkbook.Activate
 
  ' Unfreeze the screen
  Application.ScreenUpdating = True
 
End Sub

 

[mriveraCP]

Thank you Vladimir! When I copied & pasted the code above, it displayed an error message: "Run time error 9 , subscript out of range" and it highlighted the line starting with FName = "C:\VSA....etc. Am I missing a step? Thanks again!

 

[mriveraCP]

Actually, when I tried this again, it worked!!! Thank you for taking the time to help me out, I sincerely appreciate it!!!

 

[ZVI]

You are very welcome and happy coding!