Making for adding and naming a new sheet
 

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.

Making for adding and naming a new sheet
 

'---------------------
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.

Making for adding and naming a new sheet
 

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.

Making for adding and naming a new sheet
 

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.

Making for adding and naming a new sheet
 

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.

Making for adding and naming a new sheet
 

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

Making for adding and naming a new sheet
 

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

Making for adding and naming a new sheet
 

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?

Making for adding and naming a new sheet
 

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?

Making for adding and naming a new sheet
 

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?

Making for adding and naming a new sheet
 

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.

Making for adding and naming a new sheet
 

'---------------------------------------------------
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.

Making for adding and naming a new sheet
 

You are Awesome! Thanks a lot

Making for adding and naming a new sheet
 

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?