'merge' sheet and adding new sheets; shname()

[Lindie]

For a club we keep a simple record of weekly subscriptions- see attached Clubbook.xls. This is an Excel 2003 book. This is maintained by some-one who is not very computer savy.

1. We produce a weekly page showing who paid what.
I would like to do this automatically with the sheet picking up the data from one particular week. I know how to do this in Access but not in Excel.

2. When a member leaves we archive their contributions sheet to an archive workbook. I guess this could be done using some VBA.

I'd be ever so grateful with some tips on how to achieve this.
Lindie

[Lindie]

In the absence of any replies so far, here is what I think should be included:
On the Mergedatasheet: names of the individual sheets by using 'shname',
then showing the current balance cell of every of those sheets by some fashion using lat row/column. How to do this is what I just don't know.
Perhaps using a userform where the user can type in the start date of the week and then the mergesheet returns all current sheets with the balance for that week.
I just don;t know where to starts folks.
Please point me in the right direction
Lindie

[Lindie]

Having had no replies to my previous post I have tried to muddle along.
The attachment shows- I hope- what I'm trying to do.

sheets with club members' names
a master sheet to base new sheets on
a 'merge' sheet showing who on a weekly bases has paid
merge sheet showing the balance of each members account
I have muddled with shname()
I cannot work out how to get the weekly 'merge' sheet to show the all he names and automatically delete those of old members
I have used a function to return the last entry in a column, the balanceAll this just seems too mucky. What is a neater way of doing all this?
Lindie

[lucas]

Threads merged.

You seem to be asking for a complete solution and it's a rather large task especially considering the layout. You should take it a step at a time and ask specific questions to solve one problem at a time.

[Lindie]

I guess that's where I have gone wrong, not breaking it down. thanks for that hint.
step 1: how do i get a 'merge' sheet with: name and balance of the individual members.
L

[lucas]

Is the layout set in stone?