Simplify code before close

[ipbr21054]

Morning,

I assume the code supplied below is what you require ?

When i close the sheet i see the below code running & each page is flickered for me to view.
Can it be simplified or changed so when i click close it then does its thing without the visual effect that i currently see each time ?

Private Sub Workbook_BeforeClose(Cancel As Boolean)Sheets("Sheet1").Range("P1:U1").Copy Sheets("Sheet1").Range("E6")
Sheets("Sheet1").Range("P2").Copy Sheets("Sheet1").Range("E7")


Sheets("DR SITE").Activate
ActiveSheet.Range("E6:J6").ClearContents
Range("E7").Select


Sheets("EBAY").Activate
ActiveSheet.Range("E6:J6").ClearContents
Range("E7").Select


Sheets("DR SITE").Activate
ActiveSheet.Range("E7").ClearContents
Range("E7").Select


Sheets("EBAY").Activate
ActiveSheet.Range("E7").ClearContents
Range("E7").Select


ActiveWorkbook.Save
End Sub

 

[mole999]

hows this for a start

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Sheet1").Range("P1:U1").Copy Sheets("Sheet1").Range("E6")
Sheets("Sheet1").Range("P2").Copy Sheets("Sheet1").Range("E7")

Sheets("DR SITE").Range("E6:J6").ClearContents

Sheets("EBAY").Range("E6:J6").ClearContents

Sheets("DR SITE").Range("E7").ClearContents

Sheets("EBAY").Range("E7").ClearContents
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub

 

[ipbr21054]

Perfect.

was it the screen update part that causes the issue ?

 

[mole999]

the selects changing your focus back and forth

 

[Rick Rothstein]

Perfect.

was it the screen update part that causes the issue ?

Actually, it was your activating each sheet before clearing the cells on that sheet. If you look at mole999's code, you will see that he simply clears the cells by referencing the sheet directly... doing it this way stops the visual skipping from sheet to sheet (activating a sheet displays that sheet, referencing it does not). By the way, since mole999's code does not activate any other sheets, you can probably remove the two Application.ScreenUpdating code lines without any noticeable affect.

 

[footoo]

Or if you want to select E7 on the 2 sheets before closing :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Sheet1").Range("P1:U1").Copy Sheets("Sheet1").Range("E6")
Sheets("Sheet1").Range("P2").Copy Sheets("Sheet1").Range("E7")


Sheets("DR SITE").Activate
Range("E6:J6,E7").ClearContents
Range("E7").Select


Sheets("EBAY").Activate
Range("E6:J6,E7").ClearContents
Range("E7").Select


ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub