Macro to add text to sheet names in single workbook

[Nieriel]

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

 

[SteveO59L]

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]

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]

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]

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]

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]

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

month_val = Range("a1").Value

 

[Nieriel]

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]

something like

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

 

[Nieriel]

Thank you so much - all working brilliantly