Macro not working at year end
 

I am using excel 2000

I have a spreadsheet tracking sales and income, with a sheet for each month where the sheets are named Dec06, Jan07 etc, with 6 columns, a = Item, b = Date in the format Jan07, c = Credits, d= Debits, e = Balance, f = Net Cash

At the start of each month I run a macro which creates a new sheet for the next month, it copies the current sheet, creates a new sheet which automatically renames the sheet, so if run in in December will copy the sheet Nov07 and create a sheet Dec07, it will clear all the entries from columns A-D from rows 3 down and will transfer the closing balance from the old sheet to be the opening balance on the new sheet

This has worked fine until now, when I have run the macro to create the Jan07 sheet when I get a runtime 1004 error message

My macro is as follows:

Sub AddSheet()
Dim sh As Worksheet
Dim NoOfSheets As Integer, YearTab As Integer
Dim LastSheet As String
Dim MonthTab As String
Dim NewYearTab As String
Dim NewSheetTab As String
Dim LastRowUsed As Long
NoOfSheets = 0

'Count how Many Sheets
For Each sh In ActiveWorkbook.Sheets
NoOfSheets = NoOfSheets + 1
Next sh

LastSheet = Sheets(NoOfSheets).Name
Sheets(NoOfSheets).Copy After:=Sheets(NoOfSheets)
MonthTab = Left(LastSheet, 3)
YearTab = Right(LastSheet, 2)

NewSheetTab = Format(DateAdd("m", 1, "1/" & MonthTab & "/2006"), "mmm")

If YearTab < 10 Then NewYearTab = "0" & YearTab
Sheets(NoOfSheets + 1).Name = NewSheetTab & NewYearTab

When I click Debug the last line of the macro is highlighted

What do I need to change to make this work

Thx in advance for any help

Macro not working at year end
 

Sub AddSheet()
Dim sh As Worksheet
Dim NoOfSheets As Long
Dim LastSheet As String
Dim YearTab As String
Dim MonthTab As String

'Count how Many Sheets
NoOfSheets = ActiveWorkbook.Worksheets.Count

LastSheet = Sheets(NoOfSheets).Name
Sheets(NoOfSheets).Copy After:=Sheets(NoOfSheets)
MonthTab = Left(LastSheet, 3)
YearTab = Right(LastSheet, 2)


Sheets(NoOfSheets + 1).Name = Format(DateAdd("m", 1, _
"1/" & MonthTab & "/" & Format(YearTab, "00")), "mmmyy")
End Sub




--
HTH

Bob Phillips

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

"Paul S" wrote in message
...

I am using excel 2000

I have a spreadsheet tracking sales and income, with a sheet for each
month where the sheets are named Dec06, Jan07 etc, with 6 columns, a =
Item, b = Date in the format Jan07, c = Credits, d= Debits, e =
Balance, f = Net Cash

At the start of each month I run a macro which creates a new sheet for
the next month, it copies the current sheet, creates a new sheet which
automatically renames the sheet, so if run in in December will copy the
sheet Nov07 and create a sheet Dec07, it will clear all the entries from
columns A-D from rows 3 down and will transfer the closing balance from
the old sheet to be the opening balance on the new sheet

This has worked fine until now, when I have run the macro to create the
Jan07 sheet when I get a runtime 1004 error message

My macro is as follows:

Sub AddSheet()
Dim sh As Worksheet
Dim NoOfSheets As Integer, YearTab As Integer
Dim LastSheet As String
Dim MonthTab As String
Dim NewYearTab As String
Dim NewSheetTab As String
Dim LastRowUsed As Long
NoOfSheets = 0

'Count how Many Sheets
For Each sh In ActiveWorkbook.Sheets
NoOfSheets = NoOfSheets + 1
Next sh

LastSheet = Sheets(NoOfSheets).Name
Sheets(NoOfSheets).Copy After:=Sheets(NoOfSheets)
MonthTab = Left(LastSheet, 3)
YearTab = Right(LastSheet, 2)

NewSheetTab = Format(DateAdd("m", 1, "1/" & MonthTab & "/2006"),
"mmm")

If YearTab < 10 Then NewYearTab = "0" & YearTab
Sheets(NoOfSheets + 1).Name = NewSheetTab & NewYearTab

When I click Debug the last line of the macro is highlighted

What do I need to change to make this work

Thx in advance for any help




--
Paul S

Thx Bob, that worked fine