View Full Version : 'merge' sheet and adding new sheets; shname()

04-19-2010, 02:32 PM
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.

04-21-2010, 02:38 PM
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

04-22-2010, 02:14 PM
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?

04-22-2010, 08:36 PM
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.

04-23-2010, 03:26 PM
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.

04-24-2010, 05:54 AM
Is the layout set in stone?