VBA to save multiple tabs as CSV, then save primary file as original name

[DEllis81]

Good Morning!

I have an excel file - with multiple tabs generated via power query - that i would now like to convert those individual tabs to CSV. For every CSV file created - changes the name of the active file to my designated csv name. Then at the end - I want to save the file back to the original name. However I get an error message with multiple potential reasons -

Sub SaveFiles()
'
' SaveFiles Macro
'
    Application.DisplayAlerts = False
    
    Dim MasterDirectory As String
'   MasterDirectory = ActiveWorkbook.Sheets("DirectorySetup").Range("A4")
    Dim MasterFileName As String
    MasterFileName = ActiveWorkbook.Sheets("DirectorySetup").Range("C4") & ".xlsm"
    
    Dim FBSImportDirectory As String
    FBSImportDirectory = ActiveWorkbook.Sheets("DirectorySetup").Range("A7")
    
    Range("D1").Select
    ActiveWorkbook.Save
    Sheets("PackerImport").Select
    Range("A2").Select
    ChDir "C:\FBSPacker\FBSImportFiles"
    ActiveWorkbook.SaveAs Filename:= _
        FBSImportDirectory & "PackerImport" & ".csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveSheet.Name = "PackerImport"
    Sheets("GroupMap").Select
    Range("D1").Select
    ChDir "C:\FBSPacker"
    ActiveWorkbook.SaveAs Filename:=MasterDirectory & MasterFileName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
    Application.DisplayAlerts = True
    MsgBox Prompt:="Files Saved"
End Sub

 

[DEllis81]

Sorry - my original post was inadvertantly sent and I exceeded the 10 minute limit. Then I see I copied the wrong VBA example. Following example is problematic - the intial code provided is from a differing workbook, that appears to be working.


Good Morning!

I have an excel file - with multiple tabs generated via power query - that i would now like to convert those individual tabs to CSV. For every CSV file created - changes the name of the active file to my designated csv name. Then at the end - I want to save the file back to the original name. However I get an error message with multiple potential reasons with the reason I suspect - "The workbook you are trying to save has the same name as a currently open workbook."

The resulting error occurs the two lines after the ChDir "\\SERVER2\Shared\Daryl\Fuel" (6 lines from bottom)

Also - what I don't understand - I have the same basic macro in another file, that seems to be working. Any thoughts? Always appreciate the help and support from forums!

Sub SaveFiles()
'
' SaveFiles Macro
'

'
Application.DisplayAlerts = False
Sheets("DataLog").Select
Range("A1").Select
ActiveWorkbook.Save
Sheets("GSF Application Import").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GSF Application Import.csv", FileFormat:= _
xlCSV, CreateBackup:=False
Sheets("GFF AP Import").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GFF AP Import.csv", FileFormat:=xlCSV, _
CreateBackup:=False
Sheets("GSF AR Import").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"\\SERVER2\Shared\Daryl\Fuel\Import\GSF AR Import.csv", FileFormat:=xlCSV, _
CreateBackup:=False
Sheets("DataLog").Select
ChDir "\\SERVER2\Shared\Daryl\Fuel"
ActiveWorkbook.SaveAs Filename:="\\SERVER2\Shared\Daryl\Fuel\FuelLog.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox Prompt:="Files Saved"

End Sub