Solved

VBA Excel - Add sheet and rename

Posted on 2010-11-30
4
1,302 Views
Last Modified: 2012-06-21
Dear Experts,

I have three lines in a part of a macro which simply add a new sheet and renames it

Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Orders"

My problem that sometimes when the macro add a sheet, it is not Sheet4 just Sheet3 for example, and it stops the running. Could you please advise how to do this simplier so maybe in one step adding and renaming? The target would be simply just adding a new sheet as "Orders"

thanks,
0
Comment
Question by:csehz
4 Comments
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 100 total points
Comment Utility
Try this

Sub AddWorksheet()
Worksheets.Add().Name = "Orders"
End Sub


Ed
0
 
LVL 17

Accepted Solution

by:
gtgloner earned 200 total points
Comment Utility
Sub Macro1()

    Sheets.Add
    ActiveSheet.Name = "Orders"
   
End Sub
0
 
LVL 8

Assisted Solution

by:Exceleved
Exceleved earned 200 total points
Comment Utility
Sheets.Add
activesheet.Name = "Orders"

or

with sheets.add
.Name = "Orders"
'do something else
end with
0
 
LVL 1

Author Closing Comment

by:csehz
Comment Utility
Thanks the solutions, for me seems simplier the option

Sheets.Add
ActiveSheet.Name = "Orders"

thanks,
0

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

9 Experts available now in Live!

Get 1:1 Help Now