Macro to add text to sheet names in single workbook

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26
Hi All,
I have a workbook that has around 100 sheets each with a different name and I need to add the month each sheet name so basically it will be 'current sheet name - Month' the month will obviously change each time and so I don't mind having to change the macro to tell it what to put in.
Could anyone assist me with this please?
Kind Regards
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Dim ws As Worksheet
Dim month_val As String

Sheets("sheet1").Activate
month_val = Range("a1").Value

For Each ws In ThisWorkbook.Worksheets

ws.Name = ws.Name & month_val


Next ws
 

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26
Thank you for reply however when I run it I get a 'compile error - Invalid outside procedure'
I may need to elaborate a bit more
My worksheet names are all names of people and I want to add '-Oct14' to all the names
Do I just need to manipulate the above to get it to work?
Kind Regards
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
where did you paste the code?

it needs to be within sub/end sub lines

Sub Macro1()
'
' Macro1 Macro
'
Dim ws As Worksheet
Dim month_val As String



For Each ws In ThisWorkbook.Worksheets

ws.Name = ws.Name & "Oct-15"

Next ws
'
End Sub
 

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26

ADVERTISEMENT

Yes I was inserting a module in VBA and pasting it inbetween the sub () and end sub however this one works fine - I had an error as one of my tab names once the month was added on took it over the max characters however I can amend this easily.
Thanks again for all your help - much appreciated!
 

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26
Sorry another question - is there anyway this macro can be amended so that it gives you the option to change the text (Oct-15) added to the sheet names - this will be run on a monthly basis therefore the month will need to change each time - is there a quicker way other than going in and editing the macro each time?
Thanks again
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896

ADVERTISEMENT

that's why I was originally going to store the month in the workbook, ala

month_val = Range("a1").Value
 

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26
Thanks for the reply - the problem is the workbook is an SQL export from our software and it doesn't have the month anywhere so I can't pull it from any cells which is why I need to add it onto the sheet names.
We had a macro for something else a couple of years ago that when you ran it brought up a box to input a cell range that you wanted it to run on so I was wondering if there is a way to write into the code for it to bring up a box to type which month to add to the sheet names rather than it being coded into the macro if that makes sense?
If not I can just edit the macro before running it to change the month, however my only concern is I won't always be the one running this macro and need to make it as simple as possible as some don't even know what a macro is so am a bit concerned of letting them loose in the editing fields!
Thank you very much for your help so far - I'm pretty new to Macros so any help is always appreciated!
Regards
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
something like

Dim month_val As String
month_val = InputBox("Enter Month")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top