Status: Solved
Priority: Medium
Security: Public
Views: 157
Last Modified: 2016-09-15

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

Jagwarman

Asked: 2016-09-14

Advertise Here

Microsoft Excel

1 Solution

LVL 55

Rgonzo1971Commented: 2016-09-14

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

LVL 35

Rob HensonFinance AnalystCommented: 2016-09-14

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

JagwarmanAuthor Commented: 2016-09-14

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.

JagwarmanAuthor Commented: 2016-09-14

Rgonzo, thanks for that it works great.

JagwarmanAuthor Commented: 2016-09-14

Always delivers thanks

LVL 35

Rob HensonFinance AnalystCommented: 2016-09-15

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

VBA to change Sheet names

Join & Write a Comment Already a member? Login.

Featured Post