So the following code:

Public Sub addSheet()
Sheets.Add After:=Sheets(Sheets.Count)
Range("A6").Select
End Sub

It works just fine when it is in a module. Adds the sheet and
selects cell A6. But when it is in the code attached to another
worksheet, it adds the sheet then gives a 1004 error. It would
seem that the code attached to one sheet cannot select a cell on
another sheet.

I have just spent a frustrating several hours trying to figure
out why I was getting this error. Some days I feel very naïve.

So the following code:

Public Sub addSheet()
Sheets.Add After:=Sheets(Sheets.Count)
Range("A6").Select
End Sub

It works just fine when it is in a module. Adds the sheet and
selects cell A6. But when it is in the code attached to another
worksheet, it adds the sheet then gives a 1004 error. It would
seem that the code attached to one sheet cannot select a cell on
another sheet.

I have just spent a frustrating several hours trying to figure
out why I was getting this error. Some days I feel very naïve.

I use this...


Sub InsertSheet() 'Ctrl+Shift+I
' Inserts a new sheet before the active sheet
Dim iCount%
iCount = InputBox("Enter the number of sheets to insert", "Insert
Sheets", 1)
ActiveWorkbook.Sheets.Add Befo=ActiveSheet, Count:=iCount
End Sub

...which could easily be modified for the insert position...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"puppetsock" wrote in message
...
So the following code:

Public Sub addSheet()
Sheets.Add After:=Sheets(Sheets.Count)
Range("A6").Select
End Sub

It works just fine when it is in a module. Adds the sheet and
selects cell A6. But when it is in the code attached to another
worksheet, it adds the sheet then gives a 1004 error. It would
seem that the code attached to one sheet cannot select a cell on
another sheet.

I have just spent a frustrating several hours trying to figure
out why I was getting this error. Some days I feel very naïve.

================================================

All perfectly logical. Implicit (or unqualified) range references in a
normal module refer to the active worksheet, assuming there is one. If there
isn't or say a chart sheet is active you'll get an error. However implicit
references in a worksheet module, which is a type of class or object module,
always refer to the worksheet that contains the code whether active or not.

In general it's best to limit code in object modules to events or closely
related routines, or code specific to the worksheet. But to correct your
worksheet code do

ActiveSheet.Range(etc).Activate or Select

though I'd do it like this (air code)

Dim ws as worksheet
Set ws = activeWorkbook.Worksheets.Add(etc)
(or maybe Set ws =ThisWorkbook.Add(etc)

' if not 100% sure already active
' ws.Parent.activate
ws.Range(etc).Activate

Peter T

puppetsock presented the following explanation :
So the following code:

Public Sub addSheet()
Sheets.Add After:=Sheets(Sheets.Count)
Range("A6").Select
End Sub
[...]

Put that code in a Module and call it from anywhere.

Bruno