Copy sheet without flicker
I would like to copy a template sheet to my left that will be populated by the report generator. This all works great.
However, when it comes to the sheet copy line (shown below), it is a flash where Excel appears and then disappears, although sometimes I leave a blue, partially filled excel window.
I already have it set Application.ScreenUpdating
to false and .Visible
also set to false, and I reset it - every time to minimize stalling. This outbreak is really annoying. Is there a way to prevent this?
' 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
Refresh . If I use Set shtVariance = Sheets.Add
, I don't get flash, but I lose all the nice formatting.
source to share
I may have misunderstood, but didn't you have your application.screenupdating set to false before copying?
Update Still not entirely clear what is causing the problem, but the screen flickering can be brought to the copied sheet. I got some screen flickering with a sheet containing a large image using code like yours. You can try to disable 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
source to share
I can only get one "flash" when running your code.
This is when the code in this line is executed
Application.Visible = False
This happens because Excel is hidden, the desktop is displayed instantly, and then Excel is displayed again. I would remove this line of code.
I would also check that the sheet that was active when the called code was selected is re-enabled before re-enabling screen refresh.
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
source to share