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
– Luuklag, Commented Dec 12, 2017 at 14:19

Shai Rado · Accepted Answer · 2017-12-12 14:29:05Z

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, 2017 at 14:29

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shai Rado Over a year ago

@Deepak you're welcome :) you prefer to use MonthName(Month(Date)) than Format(Date, "mmmm") ?

Shai Rado Over a year ago

@Deepak you can select only 1

Shai Rado Over a year ago

@Deepak select the answer you like most , not to mine because I nagged you

Vityata · Accepted Answer · 2017-12-12 14:27:23Z

2

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?

edited Dec 12, 2017 at 14:27

answered Dec 12, 2017 at 14:22

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

Tom · Accepted Answer · 2017-12-12 14:36:17Z

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, 2017 at 14:36

answered Dec 12, 2017 at 14:24

Tom

9,9184 gold badges34 silver badges51 bronze badges

1 Comment

Gary's Student Over a year ago

A very clever solution.

mooseman · Accepted Answer · 2017-12-12 17:58:58Z

1

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

edited Dec 12, 2017 at 17:58

answered Dec 12, 2017 at 16:05

mooseman

2,0052 gold badges17 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to change sheet name automatically in Excel VBA

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related