Create formulas in new workbook where number of worksheets in new workbook varies

Hi - I have a problem with a VBA project that I've been unable to solve on my own. I'm not sure if it's even possible to do what I want to do, but I'm hoping somebody here can help me.

I have a workbook which is used as a hub to aggregate data in a number of other workbooks. These other workbooks are generated by the hub workbook, however each of the child workbooks relates to a real world family (one worksheet for each family member), and as we know families vary in size.

On the child workbook there is an additional sheet which sums the values on each of the family members's sheets (ie Sheet1!A1 + Sheet2!A1 + Sheet3!A1 + Sheet4!A!...etc) for each of the values (there are over 200 per person), but as the family size varies, I need the sum to vary for each workbook, to ensure that (a) I am capturing each of the family members and (b) I'm not trying to add non-existent worksheets to the calculation.

Is there any way of doing this? The size of the family is a global variable entered in an InputBox by the end user - I am hoping that there may be some way I can use that value for the formulas (in a for...next loop perhaps), but I have reached the end of my knowledge!

Any help much appreciated.

Do you have a sample workbook we can see?

Hi

Thanks for the reply. I have attached a Sample Workbook (I hope I've done it right!), and below is the code that creates the workbook at present... (MemberCount is my variable for the number of people in the family). I've previously used Applications.SheetsInNewWorkbook to so that I have MemberCount+1 sheets (plus one for the totals).

For NewFamilyMember = 1 To MemberCount
Worksheets("Sheet" & NewFamilyMember).Select
ActiveSheet.Paste
Next NewFamilyMember