How to copy & paste a worksheet but change 1 bit of the formula?



I want to copy a worksheet names WK 15 that has several formulas in linking
to WK 15 of other sheets, then paste it to create another workbook but
changing all the formulas to WK16 and not WK15 but keeping all of the other
data within the pasted formula the same.

EG: SUM('Y:\AREA\PARADE\[WK 16.xls]Sheet1'!D$6) and various similar formula
to be copied and paste onto a new workbook but change [WK16.xls] to
[WK17.xls] only within all of the formula so I do not have to keep manually
changing each cells formula myself.




You can create a copy of the worksheet by holding the CTRL key down
and dragging the sheet tab along the bottom of the file window - a
little arrow will indicate where the copy will be positioned. Then you
can select all the cells in the copied sheet by clicking on the
intersection of the row and column identifiers (or by doing CTRL-A
twice). Then do CTRL-H (Find & Replace), and:

Find What: [WK 16.xls]
Replace With: [WK 17.xls]
Click Replace all.

Then you can rename the (copied) sheet as appropriate.

Hope this helps.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question