Unable to interact with original WB after creating and closing new one

I have a workbook that generates a report from various user entered parameters. Basically a new workbook is created, saved in a folder, and then closed. Once that action happens though I can't get the workbook to respond to any commands. Below is an example of it.

Everything runs okay until I try to interact with wbHome.Sheets("Sheet1") at the end. I cannot activate it, I cannot activate any of the sheets, change the visibility status of any of the sheets, etc. I can write to it though. If you were to run the same code but omit the new workbook creation/closing then the event fires without issue.

If it runs properly, I should be left with sheet1 copied and open as a new workbook. Any suggestions on what I might be missing?



Code that goes in ThisWorkbook

Please Login or Register  to view this content.

Code that goes in module

Please Login or Register  to view this content.

Reading the code I am confused about what it is trying to do.

Originally Posted by Vlad717

If it runs properly, I should be left with sheet1 copied and open as a new workbook.

The code copies ActiveSheet but never pastes it to anywhere.

ActiveSheet is the sheet you just added (Test Sheet1), not Sheet1.

You also don't merely save this file, you save a copy of it with a new name, and then close it. If you want it open, then you should just leave it open. So I'm not clear on what you want to do.

Your code variously refers to ThisWorkbook and ActiveWorkbook. In most cases, using ActiveSheet and ActiveWorkbook invites bugs. When you want to work with a specific workbook or specific sheet, it is better to explicitly refer to it.

In Email_Log_File you are passing in sActive but never use it. You declare wb but never use it. You create a new workbook with a copy of Sheet1.

Since you are closing the file in your own code, it convolutes matters to have a Close event. Just call it directly before you close.

If you want to copy Sheet1 to a new workbook, close this workbook, and leave the new one open, you need the attached update.

My apologies, I removed a lot of the code in an effort to replicate the problem with the least amount of code. What I am trying to do:

Originally Posted by 6StringJazzer

Reading the code I am confused about what it is trying to do.

The code copies ActiveSheet but never pastes it to anywhere.

ActiveSheet is the sheet you just added (Test Sheet1), not Sheet1.

Correct, I removed the code after it copies as I figured it wasn't beneficial. Normally if you were to take sheets("Sheet1").copy you would have a new workbook with just that sheet in it. This would be the activeworkbook/sheet which would then be set to a variable.


This sequence works as intended:
Workbook 1 opens (WB1)
User works on WB1
WB1 logs some actions that took place while user interacted with the book
User closes WB1
WB1 sends the "Log" worksheet via email which is kicked off from the BeforeClose event


This sequence is what I am trying to demo and is not working:
User opens WB1
User works on WB1
WB1 logs some actions
User creates a report (WB2) from WB1 which is intended to be left open (the demo above has it closing as it was just the latest attempt to get this working)
WB1 is shutdown via the sub that created WB2 (leaving the user with only WB2 open)
WB1 fires off the beforeclose event in hopes to email the log but this is where I'm stuck.