I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

Thanks for the response but I already have that part working, below is what I
am trying to do now.

I am trying to get a workbook that when it reaches 80 sheets it will
automatically take the value from the last sheet, this value is derived
from a formula in that cell, and place it in a cell on the 1st sheet, sheets
2, 3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook.

"Joel" wrote:

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

something like this

Sub Workbook_NewSheet(ByVal Sh As Object)
If Worksheets.Count = 80 Then

Worksheets(Worksheets.Count - 1).Range("A1:B7").Copy _
Destination:=Worksheets(1).Range("A1")

For sheetcount = Worksheets.Count To 6 Step -1

Worksheets(sheetcount).Delete
Next sheetcount
End If
End Sub


"jnf40" wrote:

Thanks for the response but I already have that part working, below is what I
am trying to do now.

I am trying to get a workbook that when it reaches 80 sheets it will
automatically take the value from the last sheet, this value is derived
from a formula in that cell, and place it in a cell on the 1st sheet, sheets
2, 3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook.

"Joel" wrote:

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

I'm getting an error message, do I need to declare sheetcount, and if so as
what?


"Joel" wrote:

something like this

Sub Workbook_NewSheet(ByVal Sh As Object)
If Worksheets.Count = 80 Then

Worksheets(Worksheets.Count - 1).Range("A1:B7").Copy _
Destination:=Worksheets(1).Range("A1")

For sheetcount = Worksheets.Count To 6 Step -1

Worksheets(sheetcount).Delete
Next sheetcount
End If
End Sub


"jnf40" wrote:

Thanks for the response but I already have that part working, below is what I
am trying to do now.

I am trying to get a workbook that when it reaches 80 sheets it will
automatically take the value from the last sheet, this value is derived
from a formula in that cell, and place it in a cell on the 1st sheet, sheets
2, 3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook.

"Joel" wrote:

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

If you have an "Option Explicit" statement then you need to declare the
variable

Dim sheetcount as integer

The macro need to be place on the ThisWorkbook VBA sheet.

"jnf40" wrote:

I'm getting an error message, do I need to declare sheetcount, and if so as
what?


"Joel" wrote:

something like this

Sub Workbook_NewSheet(ByVal Sh As Object)
If Worksheets.Count = 80 Then

Worksheets(Worksheets.Count - 1).Range("A1:B7").Copy _
Destination:=Worksheets(1).Range("A1")

For sheetcount = Worksheets.Count To 6 Step -1

Worksheets(sheetcount).Delete
Next sheetcount
End If
End Sub


"jnf40" wrote:

Thanks for the response but I already have that part working, below is what I
am trying to do now.

I am trying to get a workbook that when it reaches 80 sheets it will
automatically take the value from the last sheet, this value is derived
from a formula in that cell, and place it in a cell on the 1st sheet, sheets
2, 3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook.

"Joel" wrote:

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?

Here is the code I am using with your code

Private Sub UnhideSheets()
Dim sht As Object
Dim f As Worksheet
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="csb"
If ThisWorkbook.Sheets.Count = 80 Then
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
sht.Unprotect Password:="csb"
Next sht
ThisWorkbook.Sheets("Macros").Visible = False
ThisWorkbook.Sheets("Save Me").Visible = False
ThisWorkbook.Sheets("CSB Form 1257").Select
ThisWorkbook.Sheets("CSB Form 1257").Protect Password:="csb"
ThisWorkbook.Sheets("CSB Form 1257").Visible = False
ThisWorkbook.Sheets("CSB Form 12572").Select
ThisWorkbook.Sheets("CSB Form 12572").Protect Password:="csb"
ThisWorkbook.Sheets("CSB Form 12572").Visible = False
ThisWorkbook.Sheets("Create Pay Report").Select
ActiveSheet.CommandButton14.Enabled = False
ActiveSheet.CommandButton14.Visible = True
ActiveSheet.CommandButton13.Enabled = False
ActiveSheet.CommandButton13.Visible = False
ActiveSheet.CommandButton15.Enabled = False
ActiveSheet.CommandButton15.Visible = False
ActiveSheet.CommandButton17.Enabled = True
MsgBox "You Must Create a New Workbook"
Worksheets(Worksheets.Count - 1).Range("av57:bc57").Copy _
Destination:=Worksheets(1).Range("v34")
Range("v34:ae34").Select
Application.CutCopyMode = False
Selection.Merge
Worksheets(Worksheets.Count - 1).Range("bb62:bd62").Copy _
Destination:=Worksheets(1).Range("as34")
Range("as34:bb34").Select
Application.CutCopyMode = False
Selection.Merge
Range("date").Select
For sheetcount = Worksheets.Count To 6 Step -1
Worksheets(sheetcount).Delete
Next sheetcount
ThisWorkbook.Sheets("Create Pay Report").Protect Password:="csb"
ElseIf ThisWorkbook.Sheets.Count 5 Then
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
sht.Protect Password:="csb"
Next sht
For Each f In ActiveWorkbook.Worksheets
If f.Name = Sheets("Create Pay Report").Range("wksname") Then
f.Select
'flag = True
Exit For
End If
Next f
ThisWorkbook.Sheets("Create Pay Report").Visible = False
ElseIf ThisWorkbook.Sheets.Count = 5 Then
ThisWorkbook.Sheets("Create Pay Report").Visible = True
ThisWorkbook.Sheets("Create Pay Report").Protect Password:="csb"
End If

ThisWorkbook.Sheets("Macros").Visible = False
ThisWorkbook.Sheets("Save Me").Visible = False
ThisWorkbook.Sheets("CSB Form 1257").Visible = False
ThisWorkbook.Sheets("CSB Form 12572").Visible = False

ActiveWorkbook.Protect Password:="csb"
Application.ScreenUpdating = True

End Sub


"Joel" wrote:

something like this

Sub Workbook_NewSheet(ByVal Sh As Object)
If Worksheets.Count = 80 Then

Worksheets(Worksheets.Count - 1).Range("A1:B7").Copy _
Destination:=Worksheets(1).Range("A1")

For sheetcount = Worksheets.Count To 6 Step -1

Worksheets(sheetcount).Delete
Next sheetcount
End If
End Sub


"jnf40" wrote:

Thanks for the response but I already have that part working, below is what I
am trying to do now.

I am trying to get a workbook that when it reaches 80 sheets it will
automatically take the value from the last sheet, this value is derived
from a formula in that cell, and place it in a cell on the 1st sheet, sheets
2, 3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook.

"Joel" wrote:

Here is code from VBA help

Occurs when a new sheet is created in the workbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh The new sheet. Can be a Worksheet or Chart object.

Example
This example moves new sheets to the end of the workbook.

Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:= Sheets(Sheets.Count)
End Sub



"jnf40" wrote:

I am trying to get a workbook that when it reaches 80 sheets it will take the
value from the last sheet and place it in a cell on the 1st sheet, sheets 2,
3, 4 and 5 are hidden, then the workbook will take sheets 6 through 80 and
delete them and the user can continue and create new worksheets beginning
with the ending value from the previous workbook. Any suggestions?