Cannot paste into an non-active workbook

Dear All,

I am trying to paste from an active workbook(2) into an open workbook(1)

I first tried it with activiating workbook(1) and then succesfully pasted. Then I tried it without activating workbook(1) which failed.

I would appreciate knowing if this is even possible and if so how?

My attempt at this is in the workbook(2) macro

Thanks John

Yes - it is possible, I would do it like this:

Please Login or Register  to view this content.

I think you have a problem with the way you reference the range 'copytobook1' in your code when the sheet is not activated.

Thank you for your response.

Actually, "This my test file 1.xlsx" should be a in a range name single cell in "This my test file 2.xlsx" and the same applies for CopytoBook1 (e.g. cell Z1)

How to do this?

Sorry, not sure I understand - if you mean you have the names of the sheets and ranges in other cells:

Please Login or Register  to view this content.

Sorry for the confusion.

What I mean is the following:

1) In workbook "This my test file 2.xlsx" i have 2 named ranges.
2) Named_Range_1 contains the name of a workbook (e.g. "This my test file 1.xlsx")
3) Named_Range_2 contains a range (e.g. A1:A120).
4) Both Named_Range_1 and Named Range_2 can be changed in "This my test file 2.xlsx".
5) If may Macro has hard coded data then I cannot change the namae of the workbook or the range without editing the code.
6) So I am trying to make them variables so that the macro code doe snot need to be changed.

Thanks alot for your help.

Sub Macro2()
Range("copyme").Copy Workbooks(Range("Named_Range_1")).Sheets("sheet1").Range(Range("Named_Range_2"))

Note - This depends on the worksheet being called "sheet1" in all cases
End Sub

Thanks alot Yudlugar, much appreciated.

Have a nice day, John.

Sorry,

Still does not work, I get a an error type mismatch.

Also how to paste formats?

Thanks John

Ah yeah, I missed something:

Please Login or Register  to view this content.

I tested that one and should be ok. You want to paste only formats? that would be:

Please Login or Register  to view this content.

Otherwise the formats should be included in the first code

That worked, thanks alot!