copy and paste values

[Guest]

I have 11 workbooks open, wb1 through wb10 and wbA. Wb1 through wb10 have a
range of information, a1:e20, in each workbook that needs to be copied and
paste the values to wbA beginning at cell a1 in sheet1. The contents of the
range in each of the 10 workbooks, wb1 through wb10, needs to be stacked one
below the next in wbA beginning at cell a1.

How??

 

[Guest]

Try something like this. It's untested and probalby needs to have the
workbooks declared.

Sub test()
Dim wb As Workbook
Dim mySheet As Worksheet
Dim mySheeta As Worksheet

Dim myRange As Range
Dim myNewRange As Range

Set mySheeta = Nothing
On Error Resume Next
Set mySheeta = wba.Sheets("Sheet1")
On Error GoTo 0

If mySheeta = Nothing Then Exit Sub

For Each wb In Application.Workbooks
If wb.Name <> wba.Name Then
Set mySheet = Nothing

On Error Resume Next
Set mySheet = wb.Sheets("Sheet1")
On Error GoTo 0
If Not mySheet Is Nothing Then
lrow = mySheet.Cells(wb.Rows.Count, 1).End(xlUp).Row
Set myRange = mysheet.Range("a1:e20")
Set myNewRange = mysheeta.Cells(lrow + 1,
1).Resize(myRange.Rows, myRange.Columns)
myNewRange.Value = myRange.Value

End If
End If
Next wb
End Sub