Hide and Seek

[oberon.black]

I have a worksheet in my workbook that is used as a master copy. I hav
a macro that creates new worksheets based on this worksheet. I want t
hide the master worksheet so that it can not be seen an therefore i
less likly to be altered. However when I hide this sheet all of th
copies that are made from this sheet also hide.

Is there a way or a code that will allow me to hide this sheet bu
still allow the copies that are made from it to be visible?

I would really like to know. I am learning vba as I go so if this is
stupid or easy question don't hammer me to hard about it

This is the code I am using to create and copy my worksheet from

Code
-------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheets("CGS")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 2).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("SS").Copy before:=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

 

[T-®ex]

I don't see in your code the part where you hide your "master" sheet..
There seems nothing wrong w/ your code, really. Just be carefu
though:

newSheetName = ws.Cells(iRow, 1) *& "," &* ws.Cells(iRow, 2)

...

For Each ws In Worksheets
If ws.Name = newSheetName Or _
*newSheetName = ""* Or _
IsNumeric(newSheetName) Then

...

*newSheetName* will never be an empty String ("")...

Anywayz... I also once created an Excel prog wherein I have a hidde
(master) sheet (xlSheetVeryHidden). I also create copies of this hidde
sheet. What I did was to show it, create a copy, then hide it again
When I hide it (master), the copied sheet doesn't hide. So, I'm no
sure why you have the problem... I did something like:

...
TheHiddenSheet.Visible = xlSheetVisible
TheHiddenSheet.Copy After:=Worksheets(Worksheets.Count)
TheHiddenSheet.Visible = xlSheetVeryHidden
...

 

[oberon.black]

you are correct i have not hidden my sheet, and that because I do no
know how to hide it.

I am very new to vba code i construct the code piece by piece with lot
of help.

like the code you gave me i am sure it would probably solve my issu
however I do not know where or how to add it to my existing code.

please help.

I need to get a step by step book on creating vba code in excel or jus
in general. I am currently a student but have not started int
programming yet class does not kick off until 9-26-05.

thanx and hope to hear from you soo

 

[T-®ex]

A Sheet has a 'Visible' property (look in the Properties Explorer). You
can set it to 'xlSheetVisible' (default), 'xlSheetHidden', or
'xlSheetVeryHidden'. A Sheet with its Visible property set to
xlSheetHidden can be easily unhidden through menu 'Format | Sheet |
Unhide...'. A Sheet set to Visible = xlSheetVeryHidden can only be
unhidden through VBA (code or Properties Explorer).

Anywayz... In your code I noticed this:

SHEETS(\"SS\").COPY BEFORE:=SHEETS(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

Is Sheet "SS" your hidden sheet? If it is, then you can simply add:
SHEETS(\"SS\").VISIBLE = XLSHEETVISIBLE
before you copy, then, add:
SHEETS(\"SS\").VISIBLE = XLSHEETVERYHIDDEN[/B] 'OR XLSHEETHIDDEN, IF
YOU LIKE
AFTER YOU COPY...

(THE SHEET MUST BE VISIBLE BEFORE IT CAN BE COPIED...)

NOTE:
I THINK IT WOULD BE BETTER TO ACCESS THE MASTER SHEET OBJECT DIRECTLY
RATHER THAN ACCESS IT THROUGH THE SHEETS COLLECTION. SO, IF YOUR
MASTER SHEET (\"SS\") IS NAMED (CODENAME - THE '(NAME)' PROPERTY IN THE
PROPERTIES EXPLORER) \"SHEETSS\", THEN YOU'D DO SOMETHING LIKE:

SHEETSS.VISIBLE = XLSHEETVISIBLE
SHEETSS.COPY BEFORE:=SHEETS(1)
SHEETSS.VISIBLE = XLSHEETVERYHIDDEN
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

I learned VBA - Excel mostly by browsing the Help and Object Explorer
(and a lot of trial-and-error coding and, of course, help from nice
people in this forum )... I'm sorry I can't suggest a book or
another website...

 

[oberon.black]

This worked perfect...

Thanx for all your help