Improve company productivity with a Business Account.Sign Up

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

Excel VBA - issue naming sheets

Hi

I have VBA code (behind Sheet1) that adds new sheets each time it is run (see code below).
The problem is that the sheet numbers are getting bigger and bigger (eg Sheet34)
Is there a way to make the first sheet "Sheet2"
Sheets.Add after:=Sheet1
 Set NewSht1 = ActiveSheet

Open in new window

0
Murray Brown
Asked:
Murray Brown
1 Solution
 
babesiaCommented:

Dim ws As Worksheet
Set ws = Sheets.Add
ws.Name = "Sheet2"
0
 
nike_golfCommented:
Sheets.Add after:=Sheet1
 Set NewSht1 = ActiveSheet
NewSht1.name = "Sheet2"


NG,
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. I forgot to mention that I am naming the sheet already. The name is a date, but the VBE still shows each sheet as Sheet33 etc
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
nike_golfCommented:
I believe you can't change that name as it's how the system recognizes them, probably wrong here.

If you're already naming the sheets then why not refer to them by name?

NG,
0
 
TinTombStoneCommented:
You can change the vbe code name:

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"

0
 
nike_golfCommented:
I might be missing the question here could you elaborate a little more?

NG,
0
 
Rory ArchibaldCommented:
Idle curiosity - why do you need to change the CodeName?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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