June 2, 2018

Get Worksheet Name into Worksheet Range

How to get worksheet name into worksheet range automatically with VBA. In a variety of situations like when you merging data from different worksheets into a new or master worksheet, we may like to know which data came from which worksheet. Also, for example, we can use the data so obtained to create file names while saving the new file. Watch the video below:

Watch this video on my YouTube channel.

Here’s the complete VBA code to transfer the worksheet name into a worksheet cell:

Module 1 for a single sheet name transfer:

Sub displaySheetNameinRange()

Range(“A1”) = ActiveSheet.Name

End Sub

Module 2 – Transfer names of all worksheets into individual worksheet cells:

Sub getAllWorksheetNames()
Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
wksht.Range(“A1”) = wksht.Name
Next
End Sub

Further Reading:

Worksheet.Name Property (Excel)