Copy sheet without flicker

Question

I would like to copy a template sheet to the left of itself to be populated by a report generator. This all works fine.

However when it comes to the sheet copying line (shown below) their is a flash as excel appears and then disappears - though occasionally I am left with a blue, partially filled in excel window.

I already have Application.ScreenUpdating set to false and .Visible is also set to false, and I reset them everytime to minimise the disruption. This flash is really annoying. Is there anyway of preventing it?

        ' create new sheet from template sheet
        shtDeliveryVariance.Copy Before:=shtDeliveryVariance
        Set shtVariance = Sheets(shtDeliveryVariance.Index - 1)
        shtVariance.Name = "Delivery Variance " & Format(nSheetNumber, "000")

        ' minimise the flashes
        Application.Interactive = False
        Application.ScreenUpdating = False
        Application.Visible = False

Update: If I use Set shtVariance = Sheets.Add I don't get the flash, but I lose all the pretty formatting.

Answer 1

maybe I've misunderstood but shouldn't you have application.screenupdating set to false before copying?

Update Still not entirely clear on what is causing the problem but the screen flicker could be down to the copied worksheet being activated. I did get some screen flicker with a worksheet containing a large image using code like yours. You could try to disable the activation by setting Application.EnableEvents = False Maybe something like this:

Application.ScreenUpdating = False
Application.EnableEvents = False
Dim active As Worksheet
Set active = ThisWorkbook.ActiveSheet 'or somesuch

'your code here

active.Activate
Application.EnableEvents = True
Application.ScreenUpdating = true

Answer 2

I could only get a single 'flash' when running your code.

This is when the code in this line is run

Application.Visible = False

This happens because Excel is hidden, the desktop is displayed momentarily and then Excel is displayed again. I would remove that line of code.

I would also check to see that the sheet that was active when the code was called is selected again before turning the screen updating back on.

Sub Test_Flash()

Dim shtDeliveryVariance As Worksheet
Dim i As Integer

Application.Interactive = False
Application.ScreenUpdating = False

Set shtDeliveryVariance = ActiveWorkbook.Worksheets("Sheet1")
nSheetNumber = 1

For i = 1 To 100
 shtDeliveryVariance.Copy Before:=shtDeliveryVariance
 Set shtVariance = Sheets(shtDeliveryVariance.Index - 1)
 shtVariance.Name = "Delivery Variance " & Format(nSheetNumber, "000")
 nSheetNumber = nSheetNumber + i
Next i

ActiveWorkbook.Worksheets("Sheet1").Select

Application.Interactive = True
Application.ScreenUpdating = True

End Sub