Solved

VBA to change Sheet names

Posted on 2016-09-14
6
60 Views
Last Modified: 2016-09-15
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
Comment
Question by:Jagwarman
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41797578
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41797631
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
 

Author Comment

by:Jagwarman
ID: 41798056
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Jagwarman
ID: 41798059
Rgonzo, thanks for that it works great.
0
 

Author Closing Comment

by:Jagwarman
ID: 41798062
Always delivers thanks
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41799632
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question