We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
VBA to change Sheet names
Posted on 2016-09-14
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Active today
Hi,
pls try
pls try
Sub macro()
Dim bytMonth
bytMonth = Application.InputBox("Which Month?", "Select Month", 12, , , , , 1)
intYear = Year(Date)
If bytMonth < Month(Date) Then intYear = intYear + 1
NrDays = Day(DateSerial(intYear, bytMonth + 1, 0))
For Idx = 1 To NrDays
strArray = strArray & ActiveWorkbook.Worksheets(Idx).Name & "|"
Next
strArray = Left(strArray, Len(strArray) - 1)
aSheets = Split(strArray, "|")
Sheets(aSheets).Copy
For Idx = 1 To NrDays
With ActiveWorkbook.Worksheets(Idx)
.Name = Format(DateSerial(intYear, bytMonth, Idx), "d MMM yy")
.Range("A3:L" & Rows.Count).ClearContents
.Range("N3:R" & Rows.Count).ClearContents
End With
Next
End Sub
Active today
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
Thanks
Rob
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.
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month10 days, 17 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.