Restarting Workbook in Excel VBA: A Guide

Feedback

Question:

On my
excel file
I have performed various tasks and in the end, I require the last saved version. To achieve this, I shut down the excel file without saving and reopen it, which proves to be effective. Before closing the workbook, I selected a range of cells (which were created by using sorting, filtering, etc.) and now I must paste them at the end of the file after reopening it.

Upon reopening a workbook, the macro that was previously running does not resume from where it left off. Is it feasible to have the macro continue from the point it last stopped?

My proposal is to include this code in the workbook’s opening event.

     sub Auto_open()
     sheet1.activate
     ' and select the last row of the sheet1
     activesheet.paste
     end sub

Upon reopening the workbook, the subroutine is automatically executed and the contents of the cells are copied. However, it is important to note that this action does not always result in the cells being copied. If the user has previously copied data to the clipboard and then opens the file, the Auto_open sub will copy the data to the end of the sheet, which may not be the desired outcome.

Could anyone propose a viable solution to overcome this challenge?


Solution:

Consider temporarily saving your modified workbook under a new filename instead of directly overwriting it with

.SaveAs

. By doing so, you can keep both workbooks open and easily copy the desired content from the original workbook. If you no longer need the modified workbook, you can simply delete it.

The utilization of

_Open

event is a recipe for potential disasters.

The usage of

.Copy

is highly prone to errors as it involves the clipboard, which can be accessed by other applications too, resulting in unpredictable outcomes. To avoid this, I prefer using a safer method like

.Copy Destination:=...

, although occasionally I use a combination of methods to ensure error-free execution.

wbkTwo.Sheets("Sheet1").Range("A1:B5") = _
    wbkOne.Sheets("Sheet1").Range("A1:B5")

Frequently Asked Questions

Posted in Vba