Adding a worksheet

andwelch

New Member
Joined
Jun 23, 2005
Messages
20
Can someone show me the proper way to add a new worksheet to a workbook. Thanks.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
When I use Sheets.Add the debugger stops the program, highlights .Add and states "method or data member not found"? Also I noticed that when I typed Sheets. the box comes up with the choices of actions that can be picked and Add is not one of them.
 
Upvote 0
I found a mistake. I declared Dim Sheets as Worksheets. Once I took this out I no longer get the message above, but now the program stops and I get a type mismatch error?
 
Upvote 0
I'm going back to the beginning, this is the original code that worked fine in version '03. There's many sheets in the workbook and when this code is activated I want to hide all sheets from view. It is suppose to create a blank "sheet1", because a workbook needs at least 1 sheet, and hide all the rest. This workbook has a lot of user forms and modules and I had to fix a lot of them when I switch to Excel '10. This one I can't seem to get to work. Any suggested fixes or rewrites would be appreciated.

Sub Hide_Sheets()

Dim ws As Worksheet

'Program steps through each worksheet making them invisible
'leaving sheet1 visible. Workbook must have at least 1 sheet

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
 
Upvote 0
When I run the above it creates sheet1, but then stops with a type mismatch error before the For statement is executed.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,137
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top