VBA Macro to add to workbook
Sub CopyAndSaveAsCSV()
Dim originalSheet As Worksheet
Dim newWorkbook As Workbook
Dim newSheet As Worksheet
Dim savePath As String
Dim fileName As String
' Set the original sheet
Set originalSheet = ThisWorkbook.ActiveSheet
' Create a new workbook and copy data
Set newWorkbook = Workbooks.Add
Set newSheet = newWorkbook.Worksheets(1)
originalSheet.Range("A1:J55").Copy
newSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
newSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
' Get the folder path of the original file
savePath = ThisWorkbook.Path & "\JEs Import Files\"
fileName = "JEs_Import_" & originalSheet.Range("A2").Value & ".csv"
' Save the new sheet as CSV
newWorkbook.SaveAs fileName:=savePath & fileName, FileFormat:=xlCSV, CreateBackup:=False
' Close the new workbook without saving changes to the workbook structure
newWorkbook.Close SaveChanges:=False
' Inform the user
MsgBox "JEs Exported!", vbInformation
End Sub
Photo by Markus Spiske