Solved

VBA to change Sheet names

Posted on 2016-09-14

Microsoft Excel

1 solution

75 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

Comment

Question by:Jagwarman

[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

Join The Community

6 Comments

LVL 52

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 500 total points

ID: 417975782016-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 33

Expert Comment

by:Rob Henson

ID: 417976312016-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

Author Comment

by:Jagwarman

ID: 417980562016-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.

Author Comment

by:Jagwarman

ID: 417980592016-09-14

Rgonzo, thanks for that it works great.

Author Closing Comment

by:Jagwarman

ID: 417980622016-09-14

Always delivers thanks

LVL 33

Expert Comment

by:Rob Henson

ID: 417996322016-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

Featured Post

Question has a verified solution.

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

An overview of using NPV and IRR in Excel for evaluating investment decisions

Article by: Dave

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…

Microsoft Excel

Microsoft Excel - Creating Drop-Down lists that depend on the value of another list(s)

Article by: redrumkev

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…

Microsoft Excel

Building Probability Models in Excel Part 7: Modeling a Correlated Two-Fund Investment

Video by: Toby

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …