Copy from an non-activated workbook and paste into the active one

[Ilias1987]

Hello,

I am trying to copy some values from a workbook that is not open and paste them into the active workbook that contains the macro. Though it sounds relatively easy, I cannot do it.

The problem seems to be when pasting the values, at the last line of my code. I keep receiving the following message : "Run time error 438 Object doesn't support this property or method".

The code that I have written is the following:

Please Login or Register  to view this content.

Do I need to change the entire structure or could I do sth simpler to make that work?

Thanks and regards,
Ilias

[smuzoen]

You could use

Please Login or Register  to view this content.

[Ilias1987]

Thanks for the tip smuzoen!

Now the problem is that I have 100 different closed book (let us say book_1 ... book_100) and I want to copy the data from an identical sheet in each of these workbooks (let us say that the name of this sheet will be always common_sheet). This data will be pasted in 100 different sheets inside the active workbook.

The process I had in mind was

For x = 1 to 100

copy data from book_x common_sheet
paste data into active_workbook sheet_x

Next x

However, now I don't know how to combine that logic when using the path to open each closed sheet.

Any ideas please??

Thanks in advance

Ilias

[Ilias1987]

I found a way to do it, by the following code

Sub copy_paste ()

For x = 1 To 100

target_ws = "Sheet" & x
source_wb = "file path" & x & ".xls"

Set wb = ActiveWorkbook.Worksheets(target_ws)
Workbooks.Open(source_wb).Worksheets("Serv").Range("B2:K37").Copy
wb.Range("B2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Set wb2 = ActiveWorkbook
wb2.Close SaveChanges = False


Next x

End Sub

Now I am getting this message

"There is a large amount of information on the clipboard. Do you want to paste this information into another program later... "

at the end of each loop, when reaching

wb2.Close SaveChanges = False

Any additional code to get rid of that message while running the macro? I don't want to have this data available later!

Kr
Ilias

[Leith Ross]

Hello Ilias1987,

I have added a line to the macro that will clear the clipboard after the workbook is closed.

Please Login or Register  to view this content.

[Ilias1987]

Thanks again,

How can I marked it as solved? Is that an option in some sort of menu or just type [SOLVED] in a new post? Sorry for asking, I m new to the forum

[Leith Ross]

Hello Ilias1987,

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save