Copy & Paste with destination:=

[thocam]

Hi All

I am having some issues with a macro. I have an open file and would like to automatically copy range("A2") from that file and Paste it in another workbook("Final.xlsx") So I have this:

Sub Macro()
Range("A2").Select
Selection.Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Range("B2")
End Sub
But it gives an error at the line Selection.copy. Anybody seeing whats wrong?
Is it possible to change the Range("B2") to a Cells("B" & i) with a for i=2 to 10

Could someone please help me with this issue

Thanks very much!

 

[VoG]

Try

Sub Macro()
Range("A2:A10").Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Range("B2")
End Sub

 

[thocam]

That's not working. I guess its something with the destination:=Workbooks ???

Could someone see what?

 

[VoG]

Is final.xlsx open? It would have to be for the code to work.

 

[thocam]

Right, had them open in different excels.

Do you know if this should work as alternative cause it doesnt

Sub Macro ()
For i = 2 To 20
Range("A2").Select
Selection.Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Cells("B" & i)
Next i

End sub

 

[VoG]

Do you mean

Sub Macro()
For i = 2 To 20
    Range("A" & i).Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Range("B" & i)
Next i
End Sub

 

[thocam]

Well I have a Sub Openfile () which opens a file and then calls the macro we discussed, then closes the file and opens the next file,

from each file it copies cell A2 and pastes it in Final.xlsx but in a next cell, therefore the for loop.


Part of first Sub procedure:

Do While sName <> ""
Set bk = Workbooks.Open(sPath & sName)

For i = 2 To 20
Call Macro
bk.Close Savechanges:=True
sName = Dir()
Next i
Loop

So it sets i=2 and should execute Sub Macro but it still stucks at:

Sub Macro()
Range("A2").Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Range("B" & i)
End sub

 

[VoG]

Try like this

Do While sName <> ""
Set bk = Workbooks.Open(sPath & sName)
For i = 2 To 20
    Call Macro(i)
    bk.Close Savechanges:=True
    sName = Dir()
Next i
Loop


Sub Macro(i As Long)
Range("A2").Copy Destination:=Workbooks("Final.xlsx").Worksheets("Blad1").Range("B" & i)
End Sub

 

[thocam]

Thanks that did it.
Could you tell me why I should do it that way with the (i)?

Thanks again

 

[VoG]

The macro sub cannot "see" the value of i unless you pass it as a parameter.