Simplify code before close

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,641
Office Version
  1. 2007
Platform
  1. Windows
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 ?


Code:
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
hows this for a start
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,641
Office Version
  1. 2007
Platform
  1. Windows
Perfect.

was it the screen update part that causes the issue ?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
the selects changing your focus back and forth
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,331
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Or if you want to select E7 on the 2 sheets before closing :
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top