Making for adding and naming a new sheet

M

michaelberrier

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

Ardus Petus

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

HTH
 
M

michaelberrier

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

Bob Phillips

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)
 
A

Ardus Petus

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(Date),"mmmm")
'------------------------
 
K

KellTainer

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

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
 
M

michaelberrier

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

Ardus Petus

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

michaelberrier

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?
 
A

Ardus Petus

Sub test()

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

End Sub

HTH
 
M

michaelberrier

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

Ardus Petus

'---------------------------------------------------
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,
 
B

Bob Phillips

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)
 
Top