Save As - changes worksheet name

[JTF]

here is my code to save the current worksheet as a text file

.....
Sheets("Transaction File").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\UPLOAD\NCAS" & Format(Date, "yyyymmdd") & ".txt",
FileFormat:=xlTextPrinter, CreateBackup:=False

The problem is that when the code executes, the Transaction File
worksheet is renamed with whatever name is given to the file that is
being created. I need the Transaction File worksheet to remain named
Transaction File because the next time the code is run, it looks for
that worksheet.

How can I keep the code from changing the worksheet name? Thanks!!!!

 

[Simon Letten]

Copy your data to a new workbook, save that as the text file and then close it.

Dim wkbNew as Workbook

Set wkbNew=Workbooks.Add
' copy whole of the sheet
ThisWorkbook.Sheets("Transaction File").Cells.Copy
' paste to new book
wkbNew.Worksheets(1).Paste
Application.CutCopyMode=False
wkbNew.SaveAs Filename:= _
"C:\UPLOAD\NCAS" & Format(Date, "yyyymmdd") & ".txt",
FileFormat:=xlTextPrinter, CreateBackup:=False
wkbNew.Close SaveChanges:=False
Set wkbNew=Nothing

 

[JTF]

That worked wonderfully! Many, many thanks!!!!!! I never thought of
using a new workbook.