Switching Between two open Workbooks

I have a very easy problem that has me scratching my head. In my Workbook VBA code, I browse for another Workbook and open it. I need to copy and paste cells between the main Workbook and Workbook I have browsed for and now have open. The browsed workbook is called ManualImport and I am able to open it in the code below. Now that it is open I need to activate it. I am successful in coping and paste just after the Workbook is open but in the last section of the code below I get an error when I try to switch back to the Workbook that was browsed for. that error occurs after the last comment "------------copies and paste data-----------

I have tried the following:

Workbooks("ManualImport").Activate
Windows("ManualImport").Activate
Windows(ManualImport).Activate

The first is what I have in the VBA code, but all result in errors. What is correct command?

Thanks in advance!

Please Login or Register  to view this content.

your line is

Workbooks("ManualImport").Activate

it needs the .xls_ extension if the name of the file in ManualImport.xls_

or it doesn't need quotes if ManualImport is just a variable containing a string that has the extension

or it needs both if ManualImport is a variable that contains the file name but not the extension

Workbooks(ManualImport & ".xlsm").Activate

when you throw

msgbox ManualImport

in there, what does it return?

You don't need to activate or select either workbook to copy/paste between them.

Here's your code without activating/selecting.

Please Login or Register  to view this content.

PS I've assumed you are pasting to the workbook the code is in which you set a reference to here.

Please Login or Register  to view this content.

To elaborate on what Norie is saying about activating and selecting.

If a workbooks is open you don't need to activate it and select a sheet and select a range to copy the cells to another sheet in an open workbook.

So I have two open workbooks, the one with the code in it can be referred to as ThisWorkbook and will always mean the same workbook no matter which workbook is active.

The other workbook is named "One.xlsm", you will see by the code that referring to a worksheet then a sheet on the worksheet and then a range on the worksheet is all you need. I can copy data from one to the other, it doesn't matter if I have another open workbook which happens to be the active workbook. You can also assign variables for workbooks and sheets on those workbooks and ranges on those sheets, then by just referring to the range variable I am referring to the workbook, sheet in that workbook and the range all with one variable. At the end you will see that I could have just specified the workbook, sheet and range for each sheet and copied from one to the other as in Norie's example without activating or selecting anything.

Please Login or Register  to view this content.

You don't need to switch between workbooks to do this sort of thing as long as you reference things properly.

Did you try my code from post #5?

ManualImport is the path and filename of the workbook the user has selected.

In this piece of code we set a reference, quickImport, to that workbook and that can be used throughout the rest of the code.

Please Login or Register  to view this content.

Ouka

When do you need to switch between workbooks/worksheets?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread