Copying a worksheet with controls

**SJB** · 01-08-2010, 10:16 AM

I have a worksheet named "2009" that has multiple controls (option buttons, check boxes, etc). The cell links for these controls reference a worksheet named "2009 Totals".

I'd like to create new worksheets named "2010" and "2010 Totals" by copying and pasting the 2009 worksheets. The problem is when I do this, the cell links on the "2010" worksheet still reference the "2009 Totals" worksheet. Is there a way to make a mass update to the cell links or do I have to change each one individually?

**Andy Pope** · 01-08-2010, 10:56 AM

Can you not use Find/Replace?

**SJB** · 01-08-2010, 11:15 AM

Find/Replace only looks in cells. The cell links are "behind the scenes". You have to right click on a control and select Format Control to access the cell links.

**Andy Pope** · 01-08-2010, 11:42 AM

The code will be needed to update the LinkedCell property.

**SJB** · 01-08-2010, 01:39 PM

Andy -

I don't understand your last post. There are no macros and no VB code linked to the controls, if that is what you are referring to.

**davesexcel** · 01-08-2010, 09:16 PM

When you say you have controls, it is assumed you are using option buttons, check boxes, etc from the controls toolbar, which uses VBA code.
Your last response indicates there is no code, so would it be correct to say that you are using option buttons, check boxes, etc, from the forms tool bar which is formula driven?

Is it possible to rename the workbook as your 2010 workbook, then rename the sheets?

**Andy Pope** · 01-10-2010, 08:48 AM

If the links are in cells then FIND/REPLACE.

If you have controls that have properties that are linked to cells then you will need code to do a FIND/REPLACE on the properties.

If the links are in VBA code then FIND/REPLACE in VBE.

When not post example file and save those trying to help the task of second guessing?