CODEWITHSUNDEEP
excelvba
Clarkee013
Clarkee013

Reputation: 25

how to rename sheet name different from file name

I have some vba to create a new workbook with a sheet name of "SheetName", data is then transferred to the new sheet and savedas with a file name and saved as CSV format The file name is copying over the sheet name I've declared earlier which i do not want.

Private Sub TransferData()

'---Submit Button Code - Transfers data to new workbook, renames and saves to folder.

Dim SheetName As String
Dim SavePath As String
Dim FileName As String
SheetName = "IATData"
SavePath = "savedfolderpath"
FileName = IAT.SaveDateBox.Value & IAT.SaveTimeBox.Value & IAT.SaveAssessorBRIDBox & IAT.BRIDBox


'Create new workbook for saved CSV
With Application
    .SheetsInNewWorkbook = 1
    .Workbooks.Add
    .Sheets(1).Name = SheetName
End With

'Turn off Auto-Calculate
Application.Calculation = xlCalculationManual
'Turn off Alert Displays
Application.DisplayAlerts = False

'Determine Empty Row
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer the data to the master
'---Front Page---
Cells(emptyRow, 1).Value = IAT.BRIDBox.Value
Cells(emptyRow, 2).Value = IAT.AgentNameBox.Value

'Save new workbook etc.
ActiveWorkbook.SaveAs FileName:=SavePath & FileName, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks("IAT Form.xlsm").Activate
MsgBox "Details sent to data folder under: " & FileName

End Sub

Would expect the new workbook to have a sheet name "IATData" and the file name to be made up of the cell reference noted above, but the worksheet keeps being named as the filename as well.

Upvotes: 2

Views: 2398

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

When you save the new workbook as .CSV, the sheetname is lost. The file is just a text csv file. When this .csv is re-opened by Excel, the sheetname will default to the filename.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

There is no "sheet name" in a CSV: it's a plain-text file format and the sheet name always shows as the filename.

CSV has nothing other than data - there's no place for Excel to put a sheet name (in fact there's not even a "sheet" in a CSV). If you open your CSV in notepad you can see what I mean.

Upvotes: 2

Related Questions