Excel Macro: error when pasting - moving down 10 rows - pasting - 10 rows - pasting, etc

Question

I'm attempting to copy data from three workbooks (one at a time) from the same range (A4:A8) and paste them into a new workbook. The only catch is I'm trying to make the paste part move down by 10 lines each loop.

I'm receiving an error message on the PasteSpecial line. I'm not sure why?

(I'm an Excel macro novice, but I'm giving it a go)

Any guidance/pointers is greatly appreciated.

Many thanks

Mike.

Dim TestWorkbook as variant,
Dim mytest as variant
TestWorkbook= array(“test1”, “test2”, “test3”) ‘3 source files
i = 0
 For Each mytest  In TestWorkbook 
 Workbooks.Open Filename:="S:\ExcelWork\" + TestWorkbook + "_Work.xls"
 Range("a4:a8").Copy
 ActiveWorkbook.Close
 Workbooks.Open Filename:="S:\Result_Workbook.xls"
 Range("l" & 5 + i).PasteSpecial Paste:=xlPasteValues `THE PROBLEM IS HERE
 i = i + 10  
Next

Answer 1

Several problems fixed 'See comments

Sub a()
 Dim TestWorkbook As Variant
 Dim mytest As Variant
 Dim s As String
 TestWorkbook = Array("test1", "test2", "test3")
 i = 1  'Start with 1
 Workbooks.Open Filename:="S:\Result_Workbook.xls" 'open only once
 For Each mytest In TestWorkbook
     s = mytest & ".xls"
     Workbooks.Open "c:\" & s
     Workbooks(s).Activate 'Remember to Activate
     Range("a4:a8").Copy
     ' Now paste properly
     Workbooks("Result_Workbook.xls").Worksheets("Sheet1").Range("l" & 5 + i).PasteSpecial Paste:=xlPasteValues
     ActiveWorkbook.Close SaveChanges:=False 'close after pasting
     i = i + 10
 Next
 End Sub