Dear All,

I have 10 rows of single columned data in identical places on three
different worksheets, in the same workbook. (ie. I have 30 cells of data -
10 cells by 3 sheets).

I wish to loop through both the 3 worksheets and the 10 celled range and
collect their data, then in place this data in a single row on a fourth
worksheet.

For some reason (basically not yet skilled enough!!) I can only seem to loop
through the 3 workbooks and only collect the data from the last sheet (ie.
collect only the last 10 cells).

My array needs to accommodate 30 elements, I'm just getting the last 10....

The first 3 sheets are basically regional data, the 4th sheet a data summary
page.

I unfortunately don't have the code I used with me to show you (it is at
work), but does anybody have any suggestions?

Kindest Regards,

andy m

Hello!

Hard to say without seeing the code.

But a very quick guess:

I assume you use 2 loops. The outer loop goes through the 3 sheets. Th
inner loop goes through the 10 cells for each sheet.
Maybe you are resetting the variable that points to the target ro
inside loop 1. That would cause the values from sheet one to b
overwritten by the values from sheet two, and these values again ge
overwritten by the values from sheet three. That would match you
description ....

Ro

--
Message posted from http://www.ExcelForum.com

Sub Button1_Click()

i = 1
For Each sht In Worksheets
If sht.Name < "Sheet4" Then
For Each cell In sht.Range("A1:A10")
ActiveSheet.Cells(1, i) = cell
i = i + 1
Next
End If
Next

End Sub

- Manges

--
Message posted from http://www.ExcelForum.com

Sub Button1_Click()

i = 1
For Each sht In Worksheets
If sht.Name < "Sheet4" Then
For Each cell In sht.Range("A1:A10")
ActiveSheet.Cells(1, i) = cell
i = i + 1
Next
End If
Next

End Sub


this considers that you have data in 3 sheets and you have a total of
sheets only. all data is put in sheet4.

- Manges

--
Message posted from http://www.ExcelForum.com