Ozgrid, Experts in Microsoft Excel Spreadsheets

Add Worksheets to Excel via VBA


Excel VBA: Add Worksheets to Excel/Add Excel Worksheets via Excel VBA Macro Code

Got any Excel Questions? Free Excel Help .

Lot's More: Excel VBA Add Excel Worksheets. See Also Add Worksheets in Month Order || Add Worksheets in Numeric Order || Sort Excel Sheets/Worksheets

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below;

Sub AddWorksheet()

Worksheets.Add().Name = "MySheet"

End Sub

If we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use;

Sub AddAsLastWorksheet()

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

End Sub

The Add Method as it applies to the Worksheet Object also has a Before Variant as well as an After Variant. However, we can only nominate a Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet after the current active Sheet.

To add, say, 4 Worksheets we could use the Count Variant;

Sub AddXWorksheets()

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

End Sub

The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet

See Also Add Worksheets in Month Order || Add Worksheets in Numeric Order || Sort Excel Sheets/Worksheets

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software


Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates