Help stop worksheets appearing

[Linus_99]

Hi,

Can someone please help with the following:

While running a userform, I need to create a new worksheet (Newname), copy some data onto it from a template sheet ("Template"), set the display size of the new worksheet to 80%, and then set a control sheet ("Control")as the active sheet to be displayed when the userform closes.

The VBA code works fine, but while the process is happening, the worksheets Newname and "Template" open and appear in the background. I have tried to use Application.Screenupdating to suppress this, but they still appear.

I would like not to see these worksheets at all, & not have the screen flash during this operation.

------------------------------------------------------------
Application.ScreenUpdating = False

Set NewWorksheet = Worksheets.Add

NewWorksheet.Name = Newname

NewWorksheet.Move after:=Sheets(Sheets.Count)

NewWorksheet.Select
ActiveWindow.Zoom = 80

Worksheets("Template").Range("A1:CS51").Copy _
(Worksheets(Newname).Range("A1:CS51"))

Worksheets("Control").Activate

Application.ScreenUpdating = True
-------------------------------------------------------------------

Any assistance would be appreciated,

Linus

 

[bydesign]

Linus

I'm suprised that Application.screenupdating = false didn't work...

Another option may be to add "Sheets(Newname).Visible = False" after the sheet is added...

But this certainly isn't as good as when screenupdating works...

 

[Linus_99]

Thanks Scott,

Tried that, but when it's hidden, I get an error message because the zoom function can't find its target etc.

Weird, because application.screenupdating generally seems to work.

FYI, I'm using Excel 2002 on XP Pro, but I don't think that's the issue.

 

[bydesign]

Linus

Try adding the "Sheets(Newname).Visible = False" after you zoom...

i.e.

Activewindow.zoom = 80
Sheets(Newname).Visible = False

 

[Linus_99]

Thanks Scott,

I think the culprit is this:

If you just do

-----------------------------------

Application.ScreenUpdating = False

Set NewWorksheet = Worksheets.Add

Worksheets("Control").Activate

Application.ScreenUpdating = True

---------------------------------------------

Then it doesn't seem to flash. I think the moving, copying and zooming operations all make the worksheet appear.

It's not the end of the world, but all that screen flashing looks pretty bad in an application.