How to stop renaming of excelsheets after running

Question

Below is a macro to save multiple sheets to different csv files BUT it keeps renaming and saving the original workbook, how to stop this.

Private Sub CommandButton1_Click()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim myName As String
myName = myName & Application.Cells(2, 2) 'cell B2 '

CurrentWorkbook = ThisWorkbook.FullName

CurrentFormat = ThisWorkbook.FileFormat

' Store current details for the workbook '

SaveToDirectory = "C:\temp\" 

' This line to correct problem with slash in Stackoverflow code formatting

For Each WS In ThisWorkbook.Worksheets
  WS.SaveAs SaveToDirectory & myName & WS.Name, xlCSV
Next

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = False
' Temporarily turn alerts off to prevent the user being prompted '
'  about overwriting the original file. '

End Sub

Answer 1

Try this:

Private Sub CommandButton1_Click()

    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String
    Dim myName As String
    Dim CurrentWorkbook As String

    ' Get the path to the curent workbook so we can open it later.
    CurrentWorkbook = ThisWorkbook.FullName

    SaveToDirectory = "C:\temp\"

    ' Turn off Excel alerts so we're not prompted if the file already exists.
    Application.DisplayAlerts = False

    For Each WS In ThisWorkbook.Worksheets
        WS.Activate                      ' Make this the current worksheet.
        myName = Application.Cells(2, 2) ' Get the contents of cell B2 for our file name.
        WS.SaveAs SaveToDirectory & myName & WS.Name, xlCSV
    Next

    ' Open the original workbook.
    Application.Workbooks.Open CurrentWorkbook

    ' Close workbook associated with the last saved worksheet.
    ThisWorkbook.Close xlDoNotSaveChanges

End Sub

It looks like the Excel SaveAs method makes the saved worksheet the active workbook, so I just close this without saving.

Answer 2

ActiveWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = False

If you are not writing anything on the workbook, why are you trying to save it?