• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to change the sheet name with a suffix of date

A

Ajit Kiran

Member
Hello guys!

Looking for a small VB code which can change the name of the worksheet in a workbook.

For eg., the below code needs to be tweaked out to show the last 2nd working day from today as a suffix to it automatically:

Code: 
Sheets("Sheet1").Name = "GRCOnedaily04112013"
Since today is 6th November (IST) the sheet must be renamed to 4th Nov as that's the 2nd last working day from today. Yet another eg., on Monday 4th Nov, the sheet must be renamed to "GRCOnedaily31102013"

Would that be possible pls? Thanks a ton for your help in advance!

Rgs,
Ajit Kiran​
 
Hi Ajit

How have you been keeping? Go with this mate. Where -2 is the variable to change if you want something different.

Code: 
Sheets("Sheet2").Name = "GRCOnedaily" & Format(Date - 2, "ddmmyyyy")

Take care

Smallman
 
hey Marcus!

Very well mate...just that winter's setting in full steam out here in India and padding up in all winter wear possible! ;) Hope u're doing good mate!

You're simply awesome lol :) Thanks a bunch again! Looks like u were born with VBA ingrained in ur cells (pun intended)! hahaha...

Ajit Kiran
 
Hi,

Further to the above, need a bit of tweaking to the code because once the sheet2 is named as "GRCOnedailyxxxxxx" it needs to be moved to the next work day. Since the above code only looks at Sheet2 and converts per the requirement at the moment, it may not hold good / would not work cos the file needs to be used everyday. Is there a way to have this tweaked out, please?

Thanks a bunch in advance!

Ajit Kiran
 
Hi Ajit

If you just run the process on the sheet you are working on it does not matter what the sheet name is.

Code: 
ActiveSheet.Name = "GRCOnedaily" & Format(Date - 2, "ddmmyyyy")

Take care

Smallman
 
Oh yes! true...that did occur to me...however, I want a particular sheet which is named "GRCOnedailyxxxxxx" to be renamed with the date suffix everyday...would that be possible pls? The reason I'm asking this is because I want this to be replicated to couple other worksheets I have in a workbook. Thanks Marcus Ajit
 
Hi Ajit

Go into the VBA Projec window and your sheets will have names like this;

Sheet1(GRCOnedailyxxxxxx)

Where GRCOnedailyxxxxxx is the name of the sheet. See that Sheet1 reference. That will stay constant no matter the sheet name. So just use this.

Code: 
Sheet1.Name = "GRCOnedaily" & Format(Date - 2, "ddmmyyyy")
Anyways this should help.

Take it easy mate

Smallman
 
That's really cool now! :) Terrific! Thanks a bunch...

Works n works brilliant!

Hey, jus ruminating upon your kind words in one of our previous threads...know what? those words still ring loud n clear in my ears! "Patience & Perseverance" is the key...will put in my best foot forward to be on this forum n learn as much as possible...u r my inspiration mate! :)

AK
 
You must log in or register to reply here.
Back
Top