VBA to change Sheet names

Jagwarman
Jagwarman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2016
Commented:
Hi,
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

Open in new window

Regards
Rob HensonFinance Analyst

Commented:
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
Jagwarman

Author

Commented:
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.
Jagwarman

Author

Commented:
Rgonzo, thanks for that it works great.
Jagwarman

Author

Commented:
Always delivers thanks
Rob HensonFinance Analyst

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial