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)
