How to get Name of the Sheet in a Cell in Excel


In this article, we will create a macro to consolidate data from different sheets, along with the sheet name.

We have data in three sheets and we want to consolidate data from all of them to the main sheet. Data from each sheet should have sheet name at the end.






On clicking “Consolidate data along with sheet name” button, data will be consolidated on the main sheet.




Code explanation

SheetCount = Application.Worksheets.Count

The above code is used to get the count of worksheet within the workbook.

LastRow = ActiveCell.SpecialCells(xlLastCell).Row

The above code is used to get the row number of the last cell.

Range("A2:F" & LastRow).Select

The above code is used to select all the data starting from cell A2.


The above code is used to copy the selected data.

Range(Selection, Cells(LastRow, 7)).Value = Sheets(Counter).Name

The above code is used to assign the sheet name to the selected cell.


Please follow below for the code

Option Explicit

Sub ConsolidateDataWithSheetName()

'Declaring variables
Dim Counter As Integer
Dim SheetCount As Integer
Dim LastRow As Long

'Disabling screen updates
Application.ScreenUpdating = False

'Getting the count of worksheets in the workbook
SheetCount = Application.Worksheets.Count

For Counter = 2 To SheetCount
        'Getting the row number of the last cell
        LastRow = ActiveCell.SpecialCells(xlLastCell).Row
        'Selecting all the data in the workbook
        Range("A2:F" & LastRow).Select
        'Copying the selected data
        LastRow = ActiveCell.SpecialCells(xlLastCell).Row
        LastRow = LastRow + 1
        'Pasting the copied data to last row on the Main sheet
        Cells(LastRow, 1).Select
        Cells(LastRow, 7).Select
        LastRow = ActiveCell.SpecialCells(xlLastCell).Row
        'Adding the sheet name along with the data
        Range(Selection, Cells(LastRow, 7)).Value = Sheets(Counter).Name

End Sub


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube