Q&A When

When you save worksheet data as a text file Excel renames the sheet with the same file name that you type for the text file?

Return to VBA Code Examples

This tutorial will cover interacting with Sheet names in VBA.

Get Sheet Name

Sheet names are stored in the Name property of the Sheets or Worksheets object. The Sheet Name is the “tab” name that’s visible at the bottom of Excel:

Get ActiveSheet Name

This will display the ActiveSheet name in a message box:

MsgBox ActiveSheet.Name

Get Sheet Name by index Number

This will display the first worksheet name in a message box:

MsgBox Sheets(1).Name

This will display the name of the last worksheet in the workbook:

MsgBox Sheets(Sheets.Count).Name

Get Sheet Name by Code Name

In the VBA Editor, there is an option to change the “code name” of a Sheet. The code name is not visible to the Excel user and can only be seen in the VBA Editor:

In VBA, when working with Sheets, you can reference the usual Tab name:

Sheets("TabName").Activate

or the VBA code name:

CodeName.Activate

Referencing the code name is desirable in case the Sheet tab name ever changes. If you allow you Excel user access to changing sheet names you should reference the code name in your VBA code so that a Sheet tab name mismatch doesn’t cause an error. Sheet code names are discussed in more detail here.

To get the Sheet name using the VBA Code name, do the following:

MsgBox CodeName.Name

Rename Sheet

You can rename Sheets by adjusting the name property of the Sheets or Worksheets object.

Rename ActiveSheet

ActiveSheet.Name = "NewName"

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!!

Rename Sheet by Name

Sheets("OldSheet").Name = "NewName"

Rename Sheet by Sheet Index Number

Here we use 1 to rename the first Sheet in the Workbook.

Sheets(1).Name = "NewName"

Rename Sheet by Code Name

This code will rename a sheet using it’s VBA code name (discussed above):

Component.Name = "NewName"

VBA Programming | Code Generator does work for you!

Check if Sheet Name Exists

We created a function to test if a Sheet with a particular name already exists.

'Test if a Range Exists on a Sheet. 'Leave range blank to test if sheet exists 'Inputs: ' WhatSheet - String Name of Sheet (ex "Sheet1") ' WhatRange (Optional, Default = "A1") - String Name of Range (ex "A1") Function RangeExists(WhatSheet As String, Optional ByVal WhatRange As String = "A1") As Boolean Dim test As Range On Error Resume Next Set test = ActiveWorkbook.Sheets(WhatSheet).Range(WhatRange) RangeExists = Err.Number = 0 On Error GoTo 0 End Function

The function will return TRUE if the Sheet exists, or FALSE if it does not.

Use the function like so:

Sub Test_SheetExists() MsgBox RangeExists("setup") End Sub

Copy Sheet and Rename

This example is from our article on Copying Sheets.

After copying and pasting a Sheet, the newly created sheet becomes the ActiveSheet. So to rename a copied Sheet, simply use ActiveSheet.Name:

Sub CopySheetRename2() Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = "LastSheet" On Error GoTo 0 End Sub

Note: We added error handling to avoid errors if the Sheet name already exists.

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples

This tutorial will demonstrate how to write to text files using VBA.

Write to a Text File

The below codes use the FileSystemObject (learn more). In order to use it, you will need to set a reference to the VB script run-time library.

Write to New Text File

With the CreateTextFile method of FileSystemObject you can create and then add content to a text file:

Sub FSOCreateAndWriteToTextFile() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Set FileToCreate = FSO.CreateTextFile("C:\Test\TestFile.txt") FileToCreate.Write "test line" FileToCreate.Close End Sub

Please note that content will not be enclosed by quotes.

Write to Existing Text File

To write to an existing text file you can use the OpenTextFile method of FileSystemObject with ForWriting mode.

Sub FSOWriteToTextFile() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForWriting) FileToWrite.Write "test line" FileToWrite.Close End Sub

Please note that you do not necessarily need FileSystemObject to write to an existing text file. The above example is shown in another way in this code below (see other example in the Data Range to Text File section):

Sub WriteToTextFile() Dim FileName As String FileName = "C:\Test\TestFile.txt" Open FileName For Output As #1 Print #1, "test line" Close #1 End Sub

Please note that using Write command instead of Print will result in having the added content enclosed by quotes. Having both commands in your macro

Write #1, "test line #1" Print #1, "test line #2"

will result in a text file like this:

Append to Text File

By changing the mode in the above code to ForAppending, a line can be added to the end of the text file:

Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForAppending)

WriteLine Method

This method appends the input string as a separate line to the existing content.

Write Method

The input string is appended on the same line as the existing content.

Learn More!!

WriteBlankLines

This method takes the number of blank lines to be written to the text file as a parameter.

This code below illustrates the difference between the different write methods:

Sub WriteMethods() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForAppending) FileToWrite.Write "test line #1 " FileToWrite.Write "test line #2" FileToWrite.WriteBlankLines (3) FileToWrite.WriteLine "test line #3" FileToWrite.WriteLine "test line #4" FileToWrite.Close End Sub

And the result:

Data Range to Text File

If you want to output a data range from your worksheet to a text file, you can use this code:

Sub OutputToTextFile() Dim FileName As String, LineText As String Dim MyRange As Range, i, j FileName = "C:\Test\TestFile.txt" 'you can specify here the text file name you want to create Open FileName For Output As #1 Set MyRange = Range("data") 'it assumes you have a data range named “data” on your worksheet For i = 1 To MyRange.Rows.Count For j = 1 To MyRange.Columns.Count LineText = IIf(j = 1, "", LineText & ",") & MyRange.Cells(i, j) 'the text file creating will have a comma separator Next j Print #1, LineText 'using Write command instead of Print will result in having your data in quotes in the output text file Next i Close #1 End Sub

Array to Text File

You can also save your array of data into a text file like this:

Sub SaveArrayToTextFile() Dim MyArray As Variant Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") MyArray = Array(Array("00", "01"), Array("10", "11"), Array("20", "21")) Set FileToCreate = FSO.CreateTextFile("C:\Test\TestFile.txt") For n = 0 To UBound(MyArray) FileToCreate.WriteLine MyArray(n)(0) & "," & MyArray(n)(1) Next FileToCreate.Close End Sub