Create new workbook, then REFERENCE it, Excel 2013

**rbrian** · 12-02-2013, 08:37 AM

Surely I'm missing something simple! A colleague made a macro that would work in 2003, 2007, and 2010, but it doesn't work in 2013. I decided to start from scratch, because I want to learn. I recorded a macro to learn how to create a new workbook with 3 named sheets, which works fine. Now I want to copy and paste values into this new sheet, to provide lightweight workbooks for reading on an iPhone, so no bogging down with macros and databases.

Whatever I try, I get error 9, subscript out if range. I've searched and searched, but everything I find is instructions for referencing named workbooks. That's fine, but Excel names them Book 1, Book 2, etc, and I can't control how many workbooks are open or even how many are going to be made that day.

There has to be a simple command to activate the new workbook that you just created, and switch back and forth.

I don't want to save the new workbook, since the file structure is different under Vista, 7, and 8, but I would like to name it if I could.

If it works in 2003, 2010, and 2013, that would be a bonus, but I don't really need 2003 - that's going to disappear along with our XP machines in April.

**Norie** · 12-02-2013, 08:49 AM

You can create a new workbook, and a reference to it like this.

Please Login or Register  to view this content.

The reference, wbNew in this example, can then be used to refer to the workbook in the rest of the code.

Not quite sure about the not saving part.

You can't do much with an unsaved workbook and that includes naming it.

**rbrian** · 12-02-2013, 09:09 AM

That's how it was before, but an unnecessarily complicated way of copying the existing names was what was broken, and my cack-handed attempts to fix it was resulting in two new workbooks, which was causing the problem. You set me on the right track though, and it's all working now. Thanks for your help.