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.

 

ArrowRawData

 

ArrowMain

 

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

 

ArrowOutput

 

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.

Selection.Copy

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
    
        Sheets(Counter).Activate
        
        Range("A2").Select
        
        '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
        Selection.Copy
        
        Sheets("Main").Activate
        
        Range("A2").Select
        LastRow = ActiveCell.SpecialCells(xlLastCell).Row
        LastRow = LastRow + 1
        
        'Pasting the copied data to last row on the Main sheet
        Cells(LastRow, 1).Select
        ActiveSheet.Paste
        
        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
    
Next

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 info@exceltip.com

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 Google PlusVisit Us On Youtube