Excel Worksheet Creation: VBA Macro to Create Sheets from Cells - Episode 2082

2,913 views · Published 24 April 2017 · 5:03 · Indexed 12 November 2026

Channel: MrExcel.com · 2017 · Education

Watch on YouTube

Microsoft Excel Tutorial: Create a worksheet for each account number shown in cells in a range. 

Welcome back to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing how to create multiple worksheets from cells using a macro. This is a common task that many Excel users face, and we have a solution that will save you time and hassle.

During one of my recent seminars, a participant named Pam shared with me her method for creating multiple worksheets. While it was clever, it still required a lot of manual work. She would insert a pivot table, filter the data, and then create a new sheet for each account number. This process was time-consuming and not very efficient. So, I decided to come up with a faster solution for Pam and for all of you.

In this video, I will show you how to use a macro to quickly create multiple worksheets from cells. This method will save you time and effort, and it can be applied to various scenarios beyond just creating worksheets for account numbers. So, let's dive in and learn how to make the most out of Excel's macro feature.

First, make sure you have the correct file format. Your file must be saved as an Excel Macro-Enabled Workbook (.xlsm) in order for the macro to work. Next, open the VBA Editor by pressing Alt+F11. If you're new to macros, don't worry, I will guide you through the process step by step. Simply follow along and you'll be a macro pro in no time.

In the VBA Editor, we will create a new module and type in the code for our macro. This code will loop through each cell in the selected range and create a new worksheet for each cell, naming the sheet after the cell's value. We will also use the Offset function to retrieve data from the adjacent cell and place it in cell B1 of the new worksheet. Once the code is entered, we can return to Excel and assign the macro to a shortcut key for easy access.

With the macro set up, we can now select the cells and press the shortcut key to create the worksheets. It's that simple! This method is not only faster, but it also eliminates the need for pivot tables and manual data manipulation. You can use this macro for various tasks, such as creating worksheets for different departments, products, or regions.

I want to thank Pam for inspiring this video and for attending my seminar in Indianapolis. I hope this solution will make your Excel tasks easier and more efficient. Don't forget to save your workbook as an .xlsm file and set the macro security to the second option for it to work properly. Thank you for watching and stay tuned for more helpful tips and tricks from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#evergreen
#excelvba

This video answers these common search terms:
how to create sheets in excel from a list
how to create sheets excel
how to create sheets in excell
how to create new sheets for each row in excel
how to create worksheets from a list in excel
how to create monthly sheets in excel
how to create worksheets on excel
how to create an all sheets list in excel
how to create multiple sheets in excel at once
how to create excel spreadsheets

Table of Contents:
(00:00) how to create sheets in Excel from a list
(00:10) How to create new sheets for each row in excel
(00:25) Pam's current method using Pivot Tables and Show Report Filter Pages
(01:12) Preparing for the macro and saving the workbook as .xlsm
(01:48) Writing the code in VBA and adding a new module
(02:51) Using the macro to create multiple worksheets on the fly
(03:46) Example of using the macro with a larger data set
(04:23) Clicking Like really helps the algorithm

Currently, she is using the Show Report Filter Pages of a pivot table which is clever, but she is looking for a faster way.
Save the workbook as XLSM
Check Macro Security with Alt+T M S & set to second level
Alt+F11
Insert Module
Type the code as shown
Alt+Q to return to Excel
Alt+F8
Select the macro & click Options
Assign to Ctrl+Shift+W

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153910/

More from this channel