Avatar of Jagwarman
Jagwarman

asked on 

VBA to change Sheet names

I have a ‘Template’ spreadsheet with 31 tabs, 1 for each day of the month. At the beginning of each month we have to create a new file with each tab having the current months dates . Could an expert provide me with VBA code that will change each TAB to the current months dates.

i.e. For Sept we have 1 Sept 16, 2 Sept 16 etc so when we go into Oct we would need 1 Oct 16, 2 Oct 16 etc

Would it be possible to have a ‘pop-up’ that could ask “what month do you want to create” that would enable us to create the file prior to month end.

Also I need to clear any data in cells A3:L? and N3:R? [? Means all data below] on each one of the sheets with a date on.

Many thanks in advance
Microsoft Excel

Avatar of undefined
Last Comment
Rob Henson
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

If this is being run from a template, can the template have just one sheet to copy the required number of times? Columns A and N on the template sheet can then be blank to start with.

Thanks
Rob
Avatar of Jagwarman
Jagwarman

ASKER

Rob that is what they do today and seem to mess it up so I prefer a macro that will create the month for me. But thanks anyway.
Avatar of Jagwarman
Jagwarman

ASKER

Rgonzo, thanks for that it works great.
Avatar of Jagwarman
Jagwarman

ASKER

Always delivers thanks
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

I was suggesting that you start with a single sheet template file from which you run a macro that creates a file with the correct number of sheets based on the template sheet.

Thanks
Rob H
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo