Getting Cell value to another worksheet
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?
|