How to duplicate a worksheet and change the linked cells for each sheet.

**henryBukowski** · 08-23-2013, 04:59 AM

This is my everest, and one of the final challenges in this huge excel project I have undertaken.

I have an excel form for use by clients, such as you might fill in at your surgery or online, populated with many many check-boxes and direct cell references for usability.

As is the way with check-boxes, each check-box has its own linked cell. I have a spreadsheet which summarises all the form data so that all client 1's answers and details fill out row 4 of the spreadsheet. Each subsequent client has his/her own row in the summary spreadsheet, from A2 all the way across to say, A50.

I need: to generate lots of replica sheets of the client form, but in such a way as to change the linked cell references for each additional sheet.

E.g

Sheet1 details, for example with client gender, age, ethnicity, link to row 1 on spreadsheet.

Sheet2 link to row 2 on spreadsheet.

Sheet3 link to row 3 on spreadsheet.

Sheet4 link to row 4 on spreadsheet.

Sheet5 link to row 5 on spreadsheet.

Sheet6 link to row 6 on spreadsheet.

I can click 'Move or copy' on my sheet to generate as many copies as I need - but to individually link each cell will take me some time and deprive me of much sanity. Can this be done?

Or to look at it another way - is there a macro that could look to see if a linked cell was already taken, and if so, move +1 cells down or across?

This may be a long-shot - but at least I know I'm in the right place!

**NeedForExcel** · 08-23-2013, 05:13 AM

Could you please attach a sample workbook?

**henryBukowski** · 08-23-2013, 05:32 AM

Thank you for your reply, Msexcelathome.

I have uploaded an example worksheet of what I am doing.

Imagine Sheets 1, 2 3 etc are client forms. They all append to Sheet4 which is a summary sheet where each client form(sheets 1, 2, 3) have their own row.

Many thanks,

H.