relative references when copying Sheets containing form controls

  • Thread starter Thread starter tom
  • Start date Start date
T

tom

I've been given a spreadsheet that I need to take copy various sheets
from, the sheets contain form controls, when I copy the sheet to a new
workbook, named ranges and so on come across fine, but the references
to the input ranges of for example the drop down box controls become
hard coded to the original sheet, I don't want this..

Is there anyway to alter the way excel copies so that the references
remain relative rather than absoloute????
 
What did you include in the addresses for those references?

I put a listbox from the forms toolbar on a worksheet.

If I used a range like: $b$1:$b$10 as the Input Range and $A$1 as the Cell
link, then I could Edit|Move or Copy Sheet|Copy the worksheet and the input
range and cell link traveled with the new sheet.

But if I used a range like sheet1!$b$1:$b$10 and sheet1!$a$1, then the listbox
input range and cell link pointed back at that original worksheet.

(I used xl2002 in my test.)
 
Thats the problem, in the original workbook, to keep things tidy the
references are stored in cell ranges on hidden sheets. Even though I
copy over both sheets, i.e. the one with the controls on and the one
with the references, the links still become absoloute.

Thanks for trying though.

I'll chat to the person who gave me the sheet and see if I can't just
copy the entire book and then delete things rather than copying things
into a blank workbook.
 
Back
Top