stop added worksheet becoming active

[crichardson]

I have a spreadsheet application where I want to create sheet fro
within vba code. I can do this easily of course but I don't wan
Excel to make a newly added worksheet active - I just want the shee
adding while remaining on the worksheet that happened to be active whe
the new sheet is added.

I've tried Application.ScreenUpdating but this doesn't have any effect
Here's how I'm adding a new sheet...

Worksheets.Add
Worksheets.Application.ActiveSheet.Name = sheetname

Thanks in advance.

Cliv

 

[Frank Kabel]

Hi clive
try something like the following
sub foo()
dim wks_old as worksheet
Dim sheetname
application.screenupdating = false
set wks_old = activesheet
'...
sheetname = "mynewnonactivesheet"
Worksheets.Add
Worksheets.Application.ActiveSheet.Name = sheetname
wks_old.activate
application.screenupdating = True
end sub

 

[Tom Ogilvy]

Dim sh as Worksheet
application.ScreenUpdating = False
set sh = Activesheet
Worksheets.Add
ActiveSheet.Name = sheetname
sh.Activate
Application.ScreenUpdating = True

 

[Kalle]

remeber = activesheet.name

Worksheets.Add
Worksheets.Application.ActiveSheet.Name = sheetname

worksheets(remember).activate

 

[crichardson]

Thanks everyone