- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Create Multiple Worksheets from a List of Cell Values
This tutorial is ideal for you if you routinely work with large datasets or need to segregate your data into worksheets depending on particular criteria. With the help of Excel, you can effectively manage and analyse data. You may save time and increase productivity by automating the process of creating many worksheets. This article will show you how to divide a list of cell values into separate worksheets depending on distinct values or other criteria.
This article will provide you clear, straightforward instructions, supported by detailed examples, to help you learn this helpful function whether you're a novice or an expert Excel user. After completing this video, you'll have the knowledge and abilities necessary to optimise the way you manage your data in Excel. So, let's get started and learn how to use Excel's power to generate numerous spreadsheets from a set of cell values. Prepare yourself to advance your data organisation!
Creating Multiple Worksheets from a List of Cell Values
Here we will first create the VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can create multiple worksheets from a list of cell values in Excel.
Step 1
Consider an Excel sheet where you have a list of items similar to the below image.
First, right-click on the sheet name and select View code to open the VBA application.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Example
Sub AddSheets() Dim xRg As Excel.Range Dim wSh As Excel.Worksheet Dim wBk As Excel.Workbook Set wSh = ActiveSheet Set wBk = ActiveWorkbook Application.ScreenUpdating = False For Each xRg In wSh.Range("A2:A6") With wBk .Sheets.Add after:=.Sheets(.Sheets.Count) On Error Resume Next ActiveSheet.Name = xRg.Value If Err.Number = 1004 Then Debug.Print xRg.Value & " already used as a sheet name" End If On Error GoTo 0 End With Next xRg Application.ScreenUpdating = True End Sub
In the code A2:A6, the range of cells containing the list
Step 3
Then click F5 to complete the task.
This is how you can create multiple worksheets from a list of cell values.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can create multiple worksheets from a list of cell values in Excel to highlight a particular set of data.