I have a workbook with sheets named "Inventory-May" or whatever the
month is. Like everyone else, I would like to automate this process.
I've found code here to do one or the other, but am having no success
sticking them together.

Basically, I need a macro to create a new sheet and rename it with a
static term, "Inventory", and then the successive month from the active
sheet.

Thanks to all.

'---------------------
Dim ws as Worksheet
....
set ws = Woksheets.add
ws.Name = "Inventory " & Format(Date,"mmmm")
'------------------------

HTH
--
AP

"michaelberrier" a écrit dans le message de news:
...
I have a workbook with sheets named "Inventory-May" or whatever the
month is. Like everyone else, I would like to automate this process.
I've found code here to do one or the other, but am having no success
sticking them together.

Basically, I need a macro to create a new sheet and rename it with a
static term, "Inventory", and then the successive month from the active
sheet.

Thanks to all.

Thanks for the nuts and bolts. That code adds the sheet before the
current sheet and inserts the current month.

I need the next successive month based on the month in the active
sheet, and I need it to appear after the current sheet.

Thanks so much for looking.

Worksheets.Add(after:=Worksheets(ActiveSheet.Index )).Name = _
"Inventory " & Format(DateSerial(Year(Date), Month(Date) + 1, 1),
"mmmm")


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"michaelberrier" wrote in message
ps.com...
Thanks for the nuts and bolts. That code adds the sheet before the
current sheet and inserts the current month.

I need the next successive month based on the month in the active
sheet, and I need it to appear after the current sheet.

Thanks so much for looking.

For next month to appear after active sheet
---------------------
Dim ws as Worksheet
....
set ws = Woksheets.add (after:=ActiveSheet)
ws.Name = "Inventory " &
Format(DateSerial(Year(date),Month(Date)+1,Day(Dat e),"mmmm")
'------------------------

"michaelberrier" a écrit dans le message de news:
...
Thanks for the nuts and bolts. That code adds the sheet before the
current sheet and inserts the current month.

I need the next successive month based on the month in the active
sheet, and I need it to appear after the current sheet.

Thanks so much for looking.

Thanks for the help. Both of those codes return syntax errors I can't
find. Any ideas?

You were right. I had not tested my code.
Here is a tested version:

HTH
--
AP

'----------------------------
Sub test()
Dim ws As Worksheet

Set ws = Worksheets.Add(after:=ActiveSheet)
ws.Name = "Inventory " & _
Format(DateSerial(Year(Date), Month(Date) + 1, Day(Date)), "mmmm")

End Sub
'---------------------------------
"michaelberrier" a écrit dans le message de news:
...
Thanks for the help. Both of those codes return syntax errors I can't
find. Any ideas?

Mine was probably NG wrap-around

Worksheets.Add(after:=Worksheets(ActiveSheet.Index )).Name = _
"Inventory " & Format(DateSerial(Year(Date), _
Month(Date) + 1, 1),"mmmm")


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"michaelberrier" wrote in message
ups.com...
Thanks for the help. Both of those codes return syntax errors I can't
find. Any ideas?

I know its a bit long and inelegant but my vba mastery is still limited
<

Sub ChangeMonth()
Dim previousSelectSheet As Worksheet
Set previousSelectedSheet = Application.ActiveSheet
previousSheetName = previousSelectedSheet.Name
Sheets.Add , previousSelectedSheet
Sheets(previousSelectedSheet.Index + 1).Select
tempMonth = Right(previousSheetName, Len(previousSheetName) - 10)
tempName = "Inventory-" & Switch(tempMonth = "January", "February",
tempMonth = "February", "March", tempMonth = "March", "April", tempMonth
= "April", "May", tempMonth = "May", "June", tempMonth = "June", "July",
tempMonth = "July", "August", tempMonth = "August", "September",
tempMonth = "September", "October", tempMonth = "October", "November",
tempMonth = "November", "December", tempMonth = "December", "January")
ActiveSheet.Name = tempName
End Sub


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=546471