How to transferred data from one workbook to another

[TSK]

Let name both workbook as A and B the worksheet in A as Summary and the
worksheet in B as 1 and 2. Worksheet in B is a form and at time it may
contained
worksheet up to 100 or more but the designed is the same.

After completing all the worksheet in B, I would required to transferred all
the data back to A and currently what I did is using = enter, which is very
time consuming especially when B contain too many worksheet.

Using my method, the cell A1 in A worksheet would reflected as
='[B.xls]1'!$H$15 and cell A2 would reflected as '[B.xls]2'!$H$15 and so on
for the rest of the cell in A

 

[Tim Williams]

'******************************************************
'This goes in a module in the Summary workbook ("A.xls")
Sub copyData()

Dim x As Integer, y As Integer
Dim shtDest, shtSrc
Dim arrCells

Set shtDest = ThisWorkbook.Sheets("Summary")

'enter all your source addresses here
arrCells = Array("C15", "E45", "F2")

For x = 1 To Workbooks("B.xls").Sheets.Count
Set shtSrc = Workbooks("B.xls").Sheets(x)
shtDest.Cells(x,1)=shtSrc.Name 'record source sheet name
For y = 0 To UBound(arrCells)
shtDest.Cells(x, y + 2).Value = _
shtSrc.Range(arrCells(y)).Value
Next y
Next x
End Sub

'*******************************************************

Tim