Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Stupid Excel tricks

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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Stupid Excel tricks

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 nave.


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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Stupid Excel tricks


"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 nave.

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

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default Stupid Excel tricks

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Toolbar Gone - STUPID STUPID STUPID!! Paige Excel Programming 1 June 3rd 09 07:22 AM
Toolbar Gone - STUPID STUPID STUPID!! Simon Lloyd[_1145_] Excel Programming 1 June 2nd 09 04:09 PM
Toolbar Gone - STUPID STUPID STUPID!! Patrick Molloy Excel Programming 0 June 2nd 09 03:52 PM
Sources of Excel Tips, Tricks & Code John James Excel Discussion (Misc queries) 6 April 3rd 06 08:51 PM
Excel TIPS&TRICKS and Samples marko Excel Discussion (Misc queries) 4 November 30th 05 08:22 PM


All times are GMT +1. The time now is 08:41 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"