[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

Add Excel worksheets using code

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
ActiveWorkbook.Save
0
dbfromnewjersey
Asked:
dbfromnewjersey
2 Solutions
 
Randy PooleCommented:
Sub AddSheets()
    Dim c As Integer
    
    For c = 1 To 10
        Application.ActiveWorkbook.Sheets.Add
    Next c
End Sub

Open in new window

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

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now