Adding a worksheet

[andwelch]

Having trouble adding a new worksheet in excel '10. Old code use to work. keep getting error 13, type mismatch. Program is to create a blank sheet1 and hide all other sheets.

Sub Hide_Sheets()
Dim ws As Worksheet

'Statement steps through each worksheet making them invisible
'leaving sheet1 visible.

Set newsheet = Worksheets.Add
newsheet.Name = "Sheet1"

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Sheet1" Then
ws.Visible = True
Else: ws.Visible = xlSheetVeryHidden
End If
Next ws

End Sub

 

[mrmmickle1]

Try changing:

[COLOR=#0000ff]Dim[/COLOR][COLOR=#333333] ws [/COLOR][COLOR=#0000ff]As Worksheet[/COLOR][COLOR=#333333] 
[/COLOR]

[COLOR=#0000ff]Dim[/COLOR] newsheet[COLOR=#0000ff] As Worksheet[/COLOR]

Not sure if this is correct. I thought I have read something along these lines (Someone Please Correct Me If I Am Wrong)....

The data type for newsheet has not been defined. If you do not define a data type for a variable I believe the default data Type is Variant.
You are trying to Set newsheet = Worksheet.Add. However, you only Set Objects. If newsheet is assumed a Variant then it can't be Set???

ws does not need to be declared as a worksheet because it is an object in a larger collection (worksheets)