Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?

To link, run this macro after you have made the new sheet and entered data in
A2:J2

The macro will add the links to the first blank row below existing data on Index
sheet

Sub linkit()
Dim rng As Range
Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng.Formula = "='" & ActiveSheet.Name & "'!A2"
Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight
End Sub

To copy, run this macro to copy contents to first blank row.

Sub copyit()
Dim rng As Range
Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
ActiveSheet.Range("A2:J2").Copy Destination:=rng
End Sub


Gord

On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar
wrote:


Dear Gord Dibben,

Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
will be in row A2 to J2. Thanks for your help.


"Gord Dibben" wrote:

I am confused by your statement "The top row a1 to a10 has cells with heading"

A1:A10 is a one column range, not the top row.

Where exactly are your headings located?

And where exactly is the data located?


Gord Dibben MS Excel MVP


On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar
wrote:

I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
with similar format. The top row a1 to a10 has cells with heading . I add new
this similar sheets continuously with changing data in b1 to b10. Now can I
make sheet1 as index of those sheets and how to transfer pasting the contents
of b1 to b10 data of every sheet into the row cells of sheet1 automatically
as and when the sheets I add?