broro183 wrote:
Hi Ben,
This work-around is just "putting the ambulance at the bottom of the
cliff" because "#REF!" means that a formula in the area you are moving
is trying to reference something that doesn't exist once it has been

The sheet works fine and there are no conflicts. I think the "REF!" comes
from an excel feature which is working against me. I am moving stuff about
in cells which contain no formula at all but have cells in a different sheet
that work with that information. I am guessing that Excel wants to adjust
the formula contents in accordance with the move but doesn't figure out what
the adjustment is a give me a "#REF!" (though I am just guessing). This has
happened in more than one sheet.

E.g. In a more simple sheet I have columns for check number (A), Amount (B),
Recipient (C), Date (D) and Status (E).
Each column contains plain data except for the "status" column which
contains this (row 15):
=IF(OR(D15="",TODAY()-30<D15),"",IF(D15+60<TODAY(),"?","*"))
)

It goes on for about 200 rows and tells me if an outstanding check is
recent, within 30 days or within 60 days of being written. Any cut and paste
of cols A to D to another row causes "#REF!" in the row the data was moved
to in the "status" cell for that row:
=IF(OR(#REF!="",TODAY()-30<#REF!),"",IF(#REF!+60<TODAY(),"?","*"))

Instead I need to copy, paste and then delete the original selection. My
question was regarding a more complicated sheet, but it applies just as much
to the above one too. The ideal is to have a function tied to the ctrl-del
shortcut that just does a copy and also stores the range somehow and then
have another function tied to the shift-ins shortcut that would paste and
then delete/clear the stored range from earlier.

I'll test the function you gave and see what I can do with it though. Thanks...

Ben


*Secondly, is this copying/moving really needed or could the
spreadsheet layout be modified to elimnate need?
If moving is needed, try adapting the macro below to use with your
existing macro (watch out for line breaks in code below):

Sub Pk_Rng()
'sourced from
http://www.experts-exchange.com/Appl..._20629300.html

Dim prompt, sDlgTitle As String
prompt = "Select a range"
sDlgTitle = "Get User Range"
On Error Resume Next
Set URng = Application.InputBox("Select range", sDlgTitle,
ActiveCell.Address, Type:=8)
MyAddr = URng.Address
' Set Pk_Rng = URng
If URng Is Nothing Then Exit Sub
'ENTER YOUR COPY, PASTE, & DELETE CODE HERE USING THE RANGE DEFINED BY
YOUR SELECTION.
End Sub

hth,
Rob Brockett
NZ