How To Create A List From Same Cells Across Multiple Sheets In Excel?

Excel is an incredibly powerful tool for organizing and analyzing data. Often, data is spread out across multiple sheets or workbooks, making it difficult to create a comprehensive list or summary. In such situations, the ability to create a list from the same cells across multiple sheets can be a real time-saver.

This tutorial will guide you through the process of creating a list from the same cells across multiple sheets in Excel. Whether you are dealing with a small number of sheets or a large dataset, this method can be easily adapted to suit your needs. By the end of this tutorial, you will have a better understanding of how to use Excel's features to quickly and efficiently create a comprehensive list from your data. So, let's get started!

Create A List From Same Cells Across Multiple Sheets

Here, we will first create a VBA module and then run the module to complete the task. So let us see a simple process to know how you can create a list from the same cells across multiple sheets in Excel.

Step 1

Consider any Excel workbook that contains multiple worksheets. First, right-click on the sheet name and select View Code to open the VBA application. then click on insert and select a module.

Right click > View code > Insert > Module.

Then copy the below-mentioned code into the text box as shown below.

Code

Sub CreateList()
Dim xAddWs As Worksheet
Dim xWs As Worksheet
Dim RngAddress As String
xTitleId = "Create a List"
RngAddress = Application.ActiveCell.Address
Set xAddWs = Application.ActiveWorkbook.Sheets.Add
xAddWs.Name = xTitleId
i = 1
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> xTitleId Then
xAddWs.Cells(i, 1).Value = xWs.Range(RngAddress).Value
i = i + 1
End If
Next
End Sub

In the code, "Create a list" is the name of the new sheet created.

Step 2

Now click on the cell where you want to list the variables and click F5 in the VBA application to complete our task.

Specified cell > F5.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a list from the same cells across multiple sheets in Excel to highlight a particular set of data.