• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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