Getting Cell value to another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jnf40 - 31 Aug 2007 15:10 GMT

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?

Reply to this Message

Joel - 31 Aug 2007 15:38 GMT

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

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

Reply to this Message

jnf40 - 31 Aug 2007 15:44 GMT

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.

> Here is code from VBA help
>
[quoted text clipped - 15 lines]
> > delete them and the user can continue and create new worksheets beginning
> > with the ending value from the previous workbook. Any suggestions?

Reply to this Message

Joel - 31 Aug 2007 15:52 GMT

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

> Thanks for the response but I already have that part working, below is what I
> am trying to do now.
[quoted text clipped - 25 lines]
> > > delete them and the user can continue and create new worksheets beginning
> > > with the ending value from the previous workbook. Any suggestions?

Reply to this Message

jnf40 - 31 Aug 2007 17:52 GMT

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

> something like this
>
[quoted text clipped - 40 lines]
> > > > delete them and the user can continue and create new worksheets beginning
> > > > with the ending value from the previous workbook. Any suggestions?

Reply to this Message

Joel - 31 Aug 2007 18:00 GMT

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.

> I'm getting an error message, do I need to declare sheetcount, and if so as
> what?
[quoted text clipped - 43 lines]
> > > > > delete them and the user can continue and create new worksheets beginning
> > > > > with the ending value from the previous workbook. Any suggestions?

Reply to this Message

jnf40 - 31 Aug 2007 18:00 GMT

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

Reply to this Message