CODEWITHSUNDEEP
vbaexcelexcel-2007
niko
niko

Reputation: 9393

How do I reopen an Excel workbook while executing a VBA Macro it contains?

I have done all sorts of things on my excel file and at the end I need to get back to the last saved version. So I close and reopen my excel file without saving, which works. Before closing the workbook I have copied a range of cells (created by using some sorting and filtering and stuff etc.) and now I need to paste them at the end of file when I reopen it.

But when we re-open a workbook, the previously running macro does not continue where it stopped. I want to run the macro where it stopped when it reopens again. Is that possible?

Well my idea is to put this code when the workbook opens:

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

because when the workbook reopens, it executes this subroutine automatically and copies the cells contents. But the drawback is we can't say its always cells will be under copied because if the user copies some data to the clipboard and then opens this file due to the Auto_open sub it copies that data to the end of sheet but that is not what I want.

Can anyone suggest a good idea to get over this problem?

Upvotes: 3

Views: 2606

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

Why don't you just temporarily .SaveAs your modified workbook under a different filename? You can then keep it open, re-open the original workbook, and do the copying with both workbooks open. Finally, delete the modified workbook if you feel so inclined.

This business with using the _Open event is just asking for disasters to happen.

Also, .Copy is a terribly error-prone method, because it uses the clipboard, which other applications can access as well, with unpredictable results at both ends. I never use .Copy alone, but sometimes use .Copy Destination:=... which is safe. But mostly, I do something like this:

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

Upvotes: 11

Related Questions