VBA Macro To Create An Excel File With Same Sheet Name As Workbook Name

[DocAElstein]

Hi Flupsie,
_ I assume by „Sheet1“ you mean the first “Tab” Counting from the Left. This can be referenced by it’s Item number , which is a consecutive count starting at 1 from the left and increasing by 1 as you count to the right. ( _.....you actually used the “shorthand” version of that yourself with the
.Sheets(1).Name= .....)

Here the modified code:

'
 Sub CreateCSVFile() '   http://www.excelfox.com/forum/showthread.php/2123-Macro-to-crete-CSV-with-same-sheetname-as-xlsm-file
'The path name:
Dim MyPath As String: MyPath = "C:\Journal Templates\"
            'MyFileName = "Adjustment JNL.csv"
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
   
    With ActiveWorkbook
    'The File name:
    Dim MyFileName As String: Let MyFileName = .Worksheets.Item(1).Name & ".csv" 'The Worksheets Collection Object is used referrencing by Item number which is a consecutive count starting at 1 from the left  and increasing by 1 as you count to the right. This Worksheet has the .Name Property applied to return the String Tab Name
                '.Sheets(1).Name = "JNL"
     .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
     .Close False
    End With
End Sub

Alan

[Flupsie]

Hi Flupsie,
_ I assume by „Sheet1“ you mean the first “Tab” Counting from the Left. This can be referenced by it’s Item number , which is a consecutive count starting at 1 from the left and increasing by 1 as you count to the right. ( _.....you actually used the “shorthand” version of that yourself with the
.Sheets(1).Name= .....)

Here the modified code:

'
 Sub CreateCSVFile() '   http://www.excelfox.com/forum/showthread.php/2123-Macro-to-crete-CSV-with-same-sheetname-as-xlsm-file
'The path name:
Dim MyPath As String: MyPath = "C:\Journal Templates\"
            'MyFileName = "Adjustment JNL.csv"
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
   
    With ActiveWorkbook
    'The File name:
    Dim MyFileName As String: Let MyFileName = .Worksheets.Item(1).Name & ".csv" 'The Worksheets Collection Object is used referrencing by Item number which is a consecutive count starting at 1 from the left  and increasing by 1 as you count to the right. This Worksheet has the .Name Property applied to return the String Tab Name
                '.Sheets(1).Name = "JNL"
     .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
     .Close False
    End With
End Sub

Alan

Thanks for the help Alan. I need one small change. The sheet name is now correct, but I need the original workbook name i.e Adjustment JNL.csv

Kindly amend your code to incorporate this