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

**Ilias1987** · 02-04-2012, 04:16 PM

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:

Sub copy_service_scores()
'
' 

Dim s_file As String 'defintion of the source file variable
Dim t_file As String 'defintion of the target file variable

s_file = "full path.xls"

    Workbooks.Open s_file
    Sheets("sheet name").Select
    Range("D10:E37").Select
    Selection.Copy
    
    Windows("the name of the workbook that is already open and contains the macro .xlsx").Activate
    Sheets("Sheet1").Select
    Range("D10").Select
    Selection.Paste

End Sub

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

Thanks and regards,
Ilias

**smuzoen** · 02-04-2012, 08:33 PM

You could use

Sub test()
Set wb = ActiveWorkbook.Worksheets("Sheet1") ' This the workbook which is open where you want to paste 
Workbooks.Open("c:\ttt\Book1.xlsx").Worksheets("Sheet1").Range("D10:E37").Copy ' change to the path of the book you want to copy from
wb.Range("D10").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

**Ilias1987** · 02-05-2012, 07:03 AM

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** · 02-05-2012, 03:29 PM

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** · 02-05-2012, 04:11 PM

Hello Ilias1987,

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

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

Application.CutCopyMode = False
Set wb2 = ActiveWorkbook
wb2.Close SaveChanges = False

Next x

End Sub

**Ilias1987** · 02-05-2012, 04:55 PM

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** · 02-05-2012, 04:58 PM

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