copy multiple unkowns

[Michael Wise]

Ok here's a little detail.

I one sheet(tab) I want all items merged from 11 other sheets(tabs).
need all items on the 11 other sheets. The number of rows will be varie
a specfic number of rows is unknown for each sheet. I do however kno
that there are a specific number of columns that is 16, this is tru
for each sheet. This is all within the same workbook. it needs to be
refreshed type senerio each time ran not a continunce of the previou
run.

So each row of data on each of the 11 sheets needs to be brought ove
to one common sheet with an unknown number of rows in the 11 sheets.
Anyone help me out here?

Thanks IN+Vance
Michae

 

[K Dales]

Assuming "merge" sheet is Sheet1, others are Sheet2-
Sheet12, and that the data you need copy is in columns A-P
starting on the 1st row:

Dim CopyRange As Range, RowNo As Integer, i As Integer

Sheets("Sheet1").Cells.Clear
For i = 2 To 4
Set CopyRange = Sheets("Sheet" & i).Range
("A1").CurrentRegion
CopyRange.Copy
With Sheets("Sheet1")
If .UsedRange.Count = 1 Then RowNo = 1 _
Else RowNo = .UsedRange.Rows.Count + 1
.Range("A" & RowNo).PasteSpecial xlPasteAll
End With
Next i

 

[K Dales]

Forgot to modify my test code for 12 sheets; that For loop
should be For i = 2 to 12, of course.