dragging the row contents of all the sheet to a Index sheet

[Narnimar]

I have a workbook of sheets containeing Titles in a4 to i4 and its Values in
a5 to i5. I need to drag the a5 to i5 of all the sheets (1,2,3,4...200)to a
"Index" sheet to make the Summery. Cell a1 to a5 is having same titeles of
all the sheets. In the "Index" sheet, a5 of the sheet '1' will be in a2 and
a5 of the sheet '2' will be in a3 if I drag. I do not know what is the right
formula?

 

[Don Guillett]

sub putemtogether()
For i = 1 To Sheets.Count
With Sheets(i)
If .Name <> "Index" Then
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
..Cells(5, 1).Resize(, 9).Copy Cells(dlr, 1)
End If
End With
Next i
end sub

 

[Max]

In Index,
Suggest you use col A to list the source data sheetnames
In your case here, you could enter in A2: Sheet1
Copy down to list all source sheetnames: Sheet2, Sheet3, ...

Then to extract the contents of A5:I5 from each source sheet
into a horiz range to the right of col A,
place this in B2:
=OFFSET(INDIRECT("'"&$A2&"'!A5"),,COLUMNS($A:A)-1)
Copy B2 across to I2, fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik