Have you ever found yourself in need of an Excel file with multiple worksheets, each customized to fit your needs? If the thought of setting this up seems overwhelming, don’t worry! This guide will take you through the process step by step how to create Macro to Create multiple worksheets from selection. So, let’s jump into how you can Create the New file with multiple Worksheets as per selection using VBA (Visual Basic for Applications).
Why Would You Need Macro to Create multiple worksheets from selection
Before we get into the actual steps, it’s important to understand why you might want to create a new file with several worksheets. Here are a few good reasons:
- Better Organization: When related data is separated into different worksheets, your work becomes more organized and easier to manage.
- Increased Efficiency: Automating the creation of worksheets saves time, especially if you’re working with repetitive tasks or large data sets.
- Personalization: Tailoring your Excel workbook to meet your specific needs makes your workflow smoother and more enjoyable.
- Step-by-Step Guide: Creating a New File with Multiple Worksheets
Just create macro to create a multiple worksheets in a new excel file using below given steps:
Macro to Create multiple worksheets from selection
Access the Developer Tab in Excel
First, you need to open Excel and get to the Developer tab. If you don’t see it right away, no worries! You can easily enable it:
- Go to File > Options > Customize Ribbon.
- In the right-hand column, find and check the box next to Developer.
- Click OK to enable the Developer tab.
- Now, click on the Developer tab and select Visual Basic to open the VBA editor.
Copy the VBA Code
Next, just copy the below given VBA code and insert a module and paste it there.:
Dim nwb As Workbook Dim nsh As Worksheet Dim rng As Range ' Create a new workbook Set nwb = Workbooks.Add Thisworkbook.Activate ' Loop through each selected cell For Each rng In Selection ' Add a new worksheet in the new workbook
Set nsh = nwb.Sheets.Add
‘ Name the worksheet based on the cell value
nsh.Name = rng.Value
Next rng
‘ Activate the new workbook
nwb.Activate
Here’s what the code does:
- Creates a New Workbook: The code starts by creating a new workbook, which is referred to as nwb.
- Loops Through Selected Cells: It then looks at each cell you’ve selected in your worksheet.
- Adds Worksheets: For each cell, the code adds a new worksheet to your new workbook and names it based on the value in the cell.
Choose Your Worksheet Names
Once your code is ready, go back to your Excel sheet and select the range of cells that contain the names you want to use for your worksheets. For instance, if you have a list with names like “Kirstin,” “Shiv,” “Sahib,” and others, simply highlight these cells.
Run the Macro
Now that you’ve selected the names and written your code, return to the VBA editor and run the macro. Instantly, a new Excel file will be created with each worksheet named according to your selection. How cool is that?
Why Automating Worksheet Creation is a Smart Move
Manually creating multiple worksheets can be tedious and time-consuming, particularly when you need to set up a lot of them. By automating this task with VBA, you gain several key benefits:
- Time-Saving: Automating the creation of worksheets allows you to focus on more important tasks, freeing up valuable time.
- Consistency: When you automate, you ensure that each worksheet is named correctly, minimizing errors.
- Scalability: This method is scalable, meaning you can apply it to any number of worksheets without extra effort.
Conclusion
Creating a new Excel file with multiple worksheets doesn’t have to be a complex or time-consuming task. With VBA, you can automate the process, making your work more efficient and organized. Whether you’re managing projects, organizing data, or just looking for a better way to work in Excel, this method offers a customizable and powerful solution. Give it a try, make it your own, and see how it can boost your productivity!
Visit our YouTube channel to learn step-by-step video tutorials
