Moving part of a worksheet to a new worksheet in the same workbook

[sirrahrc]

How can I move part of a worksheet to a new worksheet in the same workbook and maintain all relative cell references so that everything still works after it is moved?

Thanks

[6StringJazzer]

Cut and paste. Relative cell references will be preserved, that is, if you have this formula in Sheet1!A1:

=Sheet2!B2+E5

and cut and paste it to Sheet3!C3, it will be pasted as:

=Sheet2!D4+G7

I assume that's what you mean by "maintain all relative cell references".

[sirrahrc]

Sorry, I may be asking the question in the wrong way. I'll ask again in a different (and hopefully better) way:

How can I move part of worksheet 1 to worksheet 2 in the same workbook so that everything still works after it is moved?

What happens in this example cell if I just cut and paste is this:

Original cell location
Cell: AA9 in sheet 1
Formula: =SUBTOTAL(9,AA4:AA8)
Value: -£4,293.38

When moved to sheet 2
Cell: C9 in sheet 2
FormulA: =SUBTOTAL(9,C4:C8)
Value: #REF!

I need the moved data to still give the same results as it did before it was moved.

[6StringJazzer]

Rather than "maintain all ative cell references," you mean "treat all relative cell references as if they were absolute." The answer is you have to use absolute references in your formulas before you move them. Whenever you write a formula that refers to other cells, the reference should be absolute unless it has to be relative, such as when you need to copy the formula to multiple places.

=SUBTOTAL(9,$AA$4:$AA$8)

When you move it, do you want the formula to still refer to Sheet1? If so, when you paste that on another sheet it should look like this:

=SUBTOTAL(9,Sheet1!$AA$4:$AA$8)

If you want the formula to refer to Sheet2 after it's moved then we have a different problem.

[sirrahrc]

Thanks for your help.