Excel Generate List from Multiple Sheets

I am using Office 2003 and I have an excel share portfolio. I use it to keep track of my shares and unit trusts (mutual funds). Now I have a separate sheet for each share / fund, And a manually prepared summary sheet in the same spreadsheet I use for tracking, returns on dividends / interest as well as market performance and on the same summary sheet I summarise all the information, have it summarised in a date sensitive format. So I know my way around most of the usual sorts of formula found in excel.

What I am wanting is [and I have a sneaky suspicion that I will be needing a DBASE worksheet function (which I have no idea how to use), I am hoping for an option that does not need this ] to be able to generate a list of shares on a summary sheet based on the number of main sheets.

- Eg: The tabs I will have are as follows
> Summary
> Cash Account
> Shares start --->
> Share 1
> Share 2
> Share 3
> Share 4
> <--- Shares End

- In the same cell in each share tab, there is the share code, for example, C12.

- Now on the summary sheet, at A3 for instance, I want a formula to read cell C12 on Share 1, then A4, to read cell C12 on Share 2, and so on…..

- I would like it to be able to self update, so should I sell Share 2 and buy Share 5 – I want the summary sheet to be updated with the new information of share 5 and have Share 2 deleted (and this is not critical, maybe have the list alphabetically sorted)

- I can do this manually, but this is very time consuming and sometimes can have the occasional formula error as there are so many to change and I can only pick up the change a month or 2 later when it comes around to me reconciling the cash accounts for the investment account. I am hoping to automate this as much as possible

I hope there is a non DBASE way to do this, and if it is the only way, would it be possible to have an example of formula to help me understand the concept.

Hi
paste the following codes in the macro window ( Alt F11 Insert > module)

sub summary()
dim a as integer
for a = 3 to sheets.count
worksheets("Summary").cells(a,1) = worksheets(a).name
worksheets("Summary").cells(a,2) = worksheets(a).cells(12,3)
next a
end sub

run the macro.It lists the tab names in col A and corresponding C12 values i col B
Ravi

Hi Ravi

Thanks, this helps. I am still trying to understand how this works, it seems fairly simple, but since i have never used VB before it looks a ad daunting. I do have one question:

How would i modify that VB code to include a worksheet cut-off? ie: I want it to pull the names between the start & end tabs, but if something is past the end tab, it wont be included. I ask this as i have other tabs beyond it that I don't want included in the summary.

Many thanks

Scorpio

Hi
try these codes

sub summary()
dim a as integer,c as integer
dim b as string
c=2
for a = 1 to sheets.count
b= worksheets(a).name
if instr(b,"share") > 0 then
worksheets("Summary").cells(c,1) = b
worksheets("Summary").cells(c,2) = worksheets(a).cells(12,3)
c= c+1
next a
end sub

Run the macro. it will list all sheets which contains the string "share"
Ravi

Hi

I tried this code and it keeps giving me an error

"Compile Error
Next without For"

what could i be doing wrong. I am replacing the old code with the new one.

regards

Karsten

Hi
You are not wrong. I have missed a code End if It should be inserted before Next a try this modification. it should work
Ravi