Make Multiple Copies of a Sheet in Excel

Excel has a lot of options that are at our disposal to speed things up and ease up any process that we are working on. VBA is living proof of this, and we can and should always use it to our advantage for automating things.

In the example below, we will show how to make multiple copies of a certain sheet with and without VBA.

Make Multiple Sheet Copies Without VBA

For our example, we will use the sheet that will consist of random numbers from 100 to 1000:

We will simply call this sheet “Random numbers”. To copy and paste this sheet, all we need to do is click on it (its name), hold down the CTRL (Control) on the keyboard, and then drag and drop the sheet to the “+” sign. This will create another version of our sheet:

We can also use the Move or Copy option for making copies of our sheets. We will select the two sheets that we have at this point, by clicking on any of them, then on CTRL, and then choose the second one as well. Then we will right-click on any of the sheets, and choose Move or Copy:

When we click on this, a window will appear, on which we will choose the move to end option, and choose to Create a copy as well:

Finally, we will choose OK and will have two more sheets created, the same ones we already did:

Make Multiple Sheet Copies With VBA

As with everything in Excel, we can also use VBA to achieve these results. The first thing that we need to do is to open the VBA by clicking ALT + F11 on our keyboard, and then choosing Module from the Insert tab:

On the window that appears, we will insert the following code:

This code will use the Do While Loop to create copies of the active sheet as long as the count of the sheets is less than seven. This is what the code looks like in the module:

We will position ourselves in the first sheet (we do not have to, as in our example, all the sheets are the same), then we will execute the code by pressing F5 while in the module. Once the code is finished, we will have seven sheets created: