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.

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

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?

Awesome AP.

Now, if I wanted to copy the current sheet instead of creating a new
one, would I just have to replace "worksheets.add" with
ActiveSheet.Copy?

Sub test()

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

End Sub

HTH
--
AP

"michaelberrier" a écrit dans le message de news:
...
Awesome AP.

Now, if I wanted to copy the current sheet instead of creating a new
one, would I just have to replace "worksheets.add" with
ActiveSheet.Copy?

Ok, AP..last thing and I'll be out of your hair.

Is there anyway to date the successive sheets based on the month of the
previous sheet. In other words, if the macro creates "Inventory June",
then the macro fired from that sheet will make "Inventory July" and so
forth?

Thank you so much for your time.

'---------------------------------------------------
Sub CreateNextMonthSheet()
Dim dDate As Date 'Activesheet date

dDate = DateValue("1 " & _
Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 10))
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "Inventory " & _
Format(DateSerial(Year(dDate), Month(dDate) + 1, Day(dDate)), "mmmm")

End Sub
'------------------------------------------------

Cheers,
--
AP

"michaelberrier" a écrit dans le message de news:
...
Ok, AP..last thing and I'll be out of your hair.

Is there anyway to date the successive sheets based on the month of the
previous sheet. In other words, if the macro creates "Inventory June",
then the macro fired from that sheet will make "Inventory July" and so
forth?

Thank you so much for your time.

You are Awesome! Thanks a lot

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?