How to change sheet name automatically in Excel VBA

Question

In my macro, I will select a cell and insert the Formula. I will insert inside a Table. So it can be automatically draged down. But the problem is My Sheet name "December" will be changed every month. Next month it will be "January" and so on. How to change the formula.

=INDEX(Product!$L:$L; MATCH(December!$A3; Product!$I:$I; 0);COLUMNS($A:A))

The sheet name will be changed manually. Help me

Sub Macro1()
Range("Q3").Value = "=INDEX(Product!C12, MATCH(December!RC1, Product!C9, 0),COLUMNS(C1:C[-16]))"
End Sub

Make an array with the names of the month and reference them from there, or extract them from the current day. — Luuklag, Dec 12 '17 at 14:19

Vityata · Answer 1 · 2017-12-12T14:27:23.553

Put this in the worksheet module:

Private Sub Worksheet_Activate()        
    Me.Name = Format(Date, "MMMM")        
End Sub

Thus the name of the worksheet would be changed every time you click on it, depending on the current month. For the short version of the name, use Format(Date, "MMM").

If you want to get the ActiveSheet name from an Excel formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))

What Excel formula returns the sheet name?

Tom · Answer 2 · 2017-12-12T14:36:17.830

2

Use MonthName(Month(Date)) in your vba

Range("Q3").Value = "=INDEX(Product!C12, MATCH(" & MonthName(Month(Date)) & "!RC1, Product!C9, 0),COLUMNS(C1:C[-16]))"

This will currently produce:

=INDEX(Product!C12, MATCH(December!RC1, Product!C9, 0),COLUMNS(C1:C[-16]))

Or you could use INDIRECT in your excel formula

=INDEX(Product!C12, MATCH(INDIRECT(TEXT(NOW(), "mmmm")& "!RC1",False), Product!C9, 0),COLUMNS(C1:C[-16]))

This way it'll update automatically without having to run any vba. However, INDIRECT is a volatile function so it can cause slowness in your workbook.

edited Dec 12 '17 at 14:36

answered Dec 12 '17 at 14:24

Tom

9,725
3
31
48

A very clever solution. – Gary's Student Dec 12 '17 at 14:28

score 2 · Accepted Answer · answered Dec 12 '17 at 14:29

2

Since all you need is the Month as a String of the date, simply use Format(Date, "mmmm")

Implemented in your VBA code:

Range("Q3").Value = "=INDEX(Product!C12, MATCH(" & Format(Date, "mmmm") & "!RC1, Product!C9, 0),COLUMNS(C1:C[-16]))"

answered Dec 12 '17 at 14:29

Shai Rado

33,032
6
29
51

@Deepak you're welcome :) you prefer to use `MonthName(Month(Date))` than `Format(Date, "mmmm")` ? – Shai Rado Dec 12 '17 at 16:00
@Deepak you can select only 1 – Shai Rado Dec 12 '17 at 16:04
@Deepak select the answer you like most , not to mine because I nagged you – Shai Rado Dec 12 '17 at 16:06

mooseman · Answer 4 · 2017-12-12T17:58:58.540

You can get the name of the current/active sheet using

wsname = ActiveSheet.Name

Or if the sheet you need is always say the 3rd sheet then

wsname = Sheets(3).Name

But you can't just replace "December" with this variable, you need to build your formula as a text string. Your test has to be in quotes, while the variable is not.

Sub Macro1()
wsname = ActiveSheet.Name
Range("Q3").Value = "=INDEX(Product!C12, MATCH(" & wsname & "!RC1, Product!C9, 0),COLUMNS(C1:C[-16]))"
End Sub

How to change sheet name automatically in Excel VBA

4 Answers4