Data from diff sheets to one common sheet
 

Hi!!
I have this one workbook, i call the "mothership"... Now i have a few dozen
other workbooks which represent individual cities., for e.g "mumbai" "new
york" and so on... I prepare invoices in each of these city workbooks and
then move the respected worksheet to the "mothership" workbook, so that i
have one place where all the invoices (worksheets) from different cities
(workbooks) are in one place namely my "mothership" workbook. Now in this
"mothership" workbook of mine i have created an "index" worksheet which gives
me information of only a few selected items., say, invoice no., name of the
customer, the total value, the products bought by her etc.. But to get these
i have to copy each individual item., literarily (like a donkey!!) to the
index worksheet. Now what i am hoping to get from you ever so smart and
generous folks [am not buttering :)] is a way whereby the moment i move a
worksheet from say the newyork workbook to my mothership workbook, the data
from there automatically gets updated into my index..... pls pls pls pls pls
pls pls....... helppppppppppp meeeeeeeeeee!!! :(((

Data from diff sheets to one common sheet
 

David McRitchie has some code here related to building a Table of
Contents:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Sounds like what you want.

Hope this helps.

Pete

On Aug 3, 5:08 pm, Ross wrote:
Hi!!
I have this one workbook, i call the "mothership"... Now i have a few dozen
other workbooks which represent individual cities., for e.g "mumbai" "new
york" and so on... I prepare invoices in each of these city workbooks and
then move the respected worksheet to the "mothership" workbook, so that i
have one place where all the invoices (worksheets) from different cities
(workbooks) are in one place namely my "mothership" workbook. Now in this
"mothership" workbook of mine i have created an "index" worksheet which gives
me information of only a few selected items., say, invoice no., name of the
customer, the total value, the products bought by her etc.. But to get these
i have to copy each individual item., literarily (like a donkey!!) to the
index worksheet. Now what i am hoping to get from you ever so smart and
generous folks [am not buttering :)] is a way whereby the moment i move a
worksheet from say the newyork workbook to my mothership workbook, the data
from there automatically gets updated into my index..... pls pls pls pls pls
pls pls....... helppppppppppp meeeeeeeeeee!!! :(((

Data from diff sheets to one common sheet
 

Thanks a million pete... But i forgot to mention that im quite a novice at
this... The link that u gave me is a bit too much to digest :( Im completely
lost (and thats an understatement)... I guess i need nothing less then spoon
feeding here :(

"Pete_UK" wrote:

David McRitchie has some code here related to building a Table of
Contents:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Sounds like what you want.

Hope this helps.

Pete

On Aug 3, 5:08 pm, Ross wrote:
Hi!!
I have this one workbook, i call the "mothership"... Now i have a few dozen
other workbooks which represent individual cities., for e.g "mumbai" "new
york" and so on... I prepare invoices in each of these city workbooks and
then move the respected worksheet to the "mothership" workbook, so that i
have one place where all the invoices (worksheets) from different cities
(workbooks) are in one place namely my "mothership" workbook. Now in this
"mothership" workbook of mine i have created an "index" worksheet which gives
me information of only a few selected items., say, invoice no., name of the
customer, the total value, the products bought by her etc.. But to get these
i have to copy each individual item., literarily (like a donkey!!) to the
index worksheet. Now what i am hoping to get from you ever so smart and
generous folks [am not buttering :)] is a way whereby the moment i move a
worksheet from say the newyork workbook to my mothership workbook, the data
from there automatically gets updated into my index..... pls pls pls pls pls
pls pls....... helppppppppppp meeeeeeeeeee!!! :(((

Data from diff sheets to one common sheet
 

Hey Pete!! I found an alternate way of doing what i wanted to... Tried it out
in bits and pieces and i think its good enough to suit my needs.....

Sub getsheetnames()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Cells(x, 2) = ws.Name + "!"
x = x + 1
Next ws
End Sub

And along with this, =INDIRECT(B1 & "A1").

I guess you would be familiar with this... You know i never expected anyone
to even consider helping me out.... Thanks a ton once again... :))


"Ross" wrote:

Thanks a million pete... But i forgot to mention that im quite a novice at
this... The link that u gave me is a bit too much to digest :( Im completely
lost (and thats an understatement)... I guess i need nothing less then spoon
feeding here :(

"Pete_UK" wrote:

David McRitchie has some code here related to building a Table of
Contents:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Sounds like what you want.

Hope this helps.

Pete

On Aug 3, 5:08 pm, Ross wrote:
Hi!!
I have this one workbook, i call the "mothership"... Now i have a few dozen
other workbooks which represent individual cities., for e.g "mumbai" "new
york" and so on... I prepare invoices in each of these city workbooks and
then move the respected worksheet to the "mothership" workbook, so that i
have one place where all the invoices (worksheets) from different cities
(workbooks) are in one place namely my "mothership" workbook. Now in this
"mothership" workbook of mine i have created an "index" worksheet which gives
me information of only a few selected items., say, invoice no., name of the
customer, the total value, the products bought by her etc.. But to get these
i have to copy each individual item., literarily (like a donkey!!) to the
index worksheet. Now what i am hoping to get from you ever so smart and
generous folks [am not buttering :)] is a way whereby the moment i move a
worksheet from say the newyork workbook to my mothership workbook, the data
from there automatically gets updated into my index..... pls pls pls pls pls
pls pls....... helppppppppppp meeeeeeeeeee!!! :(((

Data from diff sheets to one common sheet
 

Lots of people here help others out - I'm glad you found something
that works for you.

Pete

On Aug 6, 6:38 pm, Ross wrote:
Hey Pete!! I found an alternate way of doing what i wanted to... Tried it out
in bits and pieces and i think its good enough to suit my needs.....

Sub getsheetnames()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Cells(x, 2) = ws.Name + "!"
x = x + 1
Next ws
End Sub

And along with this, =INDIRECT(B1 & "A1").

I guess you would be familiar with this... You know i never expected anyone
to even consider helping me out.... Thanks a ton once again... :))