Problem with reopening workbook with vba macro

[VVale]

Hi all!


I have problems with reopening a workbook via vba.


I need to run several macros on the same excel workbook.

Each macro deletes cells in the original workbook. Just to clarify, the original workbook contains data of 8 different users and I use the macros to create 8 workbooks, one for each user. In each workbook there must be data related to one user only. So, macro 1 keeps data of User1 (deleting data of all other users) and saves workbook1, macro2 keeps data of user 2 and saves workbook2 and so on.


Since each macro makes changes on the genuine workbook, at the end of each macro I do a SaveAs and try to reopen the original workbook to run the next macro which will make other changes. I have tried to put the vba code on a new workbook so that I can open the workbook (to be changed) and run the macros on it:


Sub Test()


Open the workbook

Run Macro1 (which makes changes, saves the updated workbook with 'Save as')

Open again the original workbook

Run Macro2


End Sub()


I used this code:

Sub test()


Workbooks.Open Filename:="C:...Report.xlsm"

Application.Run ("Report.xlsm!Delete_1")

Workbooks.Open Filename:="C:...Report.xlsm"

Application.Run ("Report.xlsm!Delete_2")


End Sub


The problem is that the first macro is run OK but then the workbook is not reopened. It seems like the vba stops running. How can I reopen the original workbook?


Can anyone help me? Many thanks in advance!


Vale

 

[Luke M]

Are the Delete_1 and Delete_2 macros scripted to do the saving and closing of the Report.xlsm workbook? It might be worth going through the code in debug mode using F8 to step through one line at a time, or using Ctrl+F8 to run the code to the cursor. The latter is helpful if you need to skip a section you already know works and is a lot of steps (such as a loop).

 

[VVale]

Hello Luke, thanks!


I have just done what you suggested. While debugging with F8 I have noticed that tha macro runs to the third line:


Application.Run ("Report.xlsm!Delete_1")


It runs the Delete_1 macro and than goes back to the first line:


Sub test()


It does not continue to the fourth line (Workbooks.Open Filename:="C:...Report.xlsm").


How can I have it run the fourth line of code?


And, yes, the Delete_1 and Delete_2 save and close Report.xlsm.


Thanks


Vale

 

[Luke M]

I believe the problem is that the workbook currently running a macro is getting closed. So, main macro calls Delete_1. Delete1 then says to do a Save As and Close the workbook. Following that logic, the workbook with current code being run gets closed. This causes the whole process to come to a halt, and is the same as hitting the Stop in the VBE.


Suggestion? Modify the Delete_1, Delete_2, etc, macros so that they just modify the workbook. Then, have your main macro do the saving and closing.