Need to Activate Newest Workbook

[TrueFangz]

Hi All,

Need some help once again.

We export a crystal report to excel, but our software only exports to xls. After the excel sheet is open, I have a macro that does a bunch of formatting, in which I need this to be in xlsx form. So what I've done at the start of my Macro is this:

Please Login or Register  to view this content.

What this does is copy the contents of "Export" file and opens a new WB, pastes the contents, and then closes the "Export" file without saving. The new WB is named "Book1.xlsx" (or "Book2, 3, 4" etc. depending on how many wb's the user has open) All is well at this point, but if they open another Export file, run my macro, it doesn't always activate the newest WB that has been created, it seems to always go back to "Book1 or 2 or whatever.

How can I make it so this code will always activate the newest WB that has been created by my macro?

(Hope this all makes sense, not sure how else to explain) Can I somehow have it so the highest "Book#" always gets activated?

[Kaper]

Please Login or Register  to view this content.

[TrueFangz]

@kaper,

Thanks for your reply, however this doesn't seem to work for me. It still brings up book1 as the visible sheet when running the macro again on a new sheet. I'll try to explain a little better what I'm doing:

When opening the "Export" file, I have a shortcut that opens an XLAM file, which contains all my macros. (The XLAM file runs, but not in a window, it's basically behind the scenes in excel) This also adds a custom tab to the Excel ribbon, which has buttons for the users to click. So when they click the button to run the formatting macro, it runs the code I am explaining.

The code I posted above is at the beginning of my sub, after this chunk of code, I have a userform pop up notifying the user that the macro is running, and will be done momentarily. The userform code activates another sub which runs a long code that takes up to 10-30 seconds to run (depending on size of export file, and computer speed)

So what's happening is when you run the macro, it runs on the "Export" file, selects all cells, copy's them, opens a new WB (Book1.xlsx) and pastes the contents, then shows the userform, then runs the long chunk of code. When this is complete, the userform closes, and "Book1.xlsx" is now active. If I keep this book open, without saving it as a different file name, and then open another "Export" file, run my macro, as soon as the userform pops up, "Book1.xlsx" is now visible on the screen behind the form. The macro, however, is running in "Book2.xlsx" which is correct. After the macro finishes and the userform goes away, "Book1.xlsx" is still active and visible, not the newest book (Book2) and so forth. If I do this a 3rd time, Book3 is created, but still "Book1" is still visible and active everytime.

[Kaper]

To make long story short , I'd even shorten (code excluding comments) my previous proposition

Please Login or Register  to view this content.

If both 2 and 3 can be in the same procedure - fine, if not put 1 at the top of module (above any procedures or subs). You can even (depending on a code and modules layout) declare it public. Then 4 shall appear preferably in the last executed procedure.

If the abowe not work for you we could make a story even shorter:
Last created/opened (and still open) workbook can be activated by simple standalone line of code:

Please Login or Register  to view this content.

[TrueFangz]

@kaper,

Hi, thanks again for your continued support. I tried all these options, still with no luck though. It's weird, when I step through the code with VBA, using F8, everything looks fine and works as intended, but when I run the macro all the way through from Excel directly, the problem persists. As soon as my user form pops up is when the visible workbook changes. I tried putting the workbooks.count code in several different places, and still no luck.

Not sure if this will help, but here's the form code:

in main sub:

Please Login or Register  to view this content.

As soon as ".show" processes, the form becomes visible in center screen above Excel window. Here's the form code:

Please Login or Register  to view this content.

When "Quote" processes, that opens up the sub where the rest of my programming is.

At the end of the "Quote" sub, I currently have this:

Please Login or Register  to view this content.

Thanks again for all your time, it's really nice of you to help!