Add Excel worksheets using code

Posted on 2014-09-02
Last Modified: 2014-09-03
I have an Excel file with one worksheet containing data.  I need a loop that will add 10 blank worksheets next to it.  The following code makes copies of the first worksheet (the one with data).  I need blank worksheets instead of copies so that I'll wind up with 1 worksheet containing data (the one that is already there) and 10 additional blank worksheets beside it. How do I modify the code to accomplish that? Thank you.

Dim varIndex
Dim wks As Worksheet, wksSource As Worksheet
Set wksSource = ActiveSheet

For varIndex = 1 To 10 Step 1
wksSource.Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Next varIndex

Application.DisplayAlerts = False
Application.DisplayAlerts = True
Question by:dbfromnewjersey
    LVL 21

    Assisted Solution

    by:Randy Poole
    Sub AddSheets()
        Dim c As Integer
        For c = 1 To 10
        Next c
    End Sub

    Open in new window

    LVL 33

    Accepted Solution

    You can add multiple worksheets without looping.
    ActiveWorkbook.Worksheets.Add After:=ActiveWorkbook.Worksheets(1), Count:=10

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now