Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
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
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
JagwarmanAuthor 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JagwarmanAuthor Commented:
Rgonzo, thanks for that it works great.
0
 
JagwarmanAuthor Commented:
Always delivers thanks
0
 
Rob HensonIT & Database AssistantCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now