imitating cut and paste

[ben]

Hello,

I would like to imitate a cut and paste which would do a copy instead of a
cut and a paste and then delete of the original selection instead of the
regular paste (i.e I would do copy, paste, delete instead of cut and paste).
By recording a macro I could see how the copy and paste are done but I don't
know how I would get a second function to remember where the original
selection was in the first function to do a delete on it after the paste.

I am wanting to do this because in a spreadsheet I have, when I do a cut and
paste it turns many cells into "REF!" and I get around it by doing a copy,
paste and delete. So I was thinking of getting around doing this each time
by combining the paste and delete part. Any help would be appreciated.

Thanks,
Ben

 

[marko]

could this hepl:

Sub Button1_Click()
Dim adr As String

Range("a1:c15").Select
adr = Selection.Address
Range("b2").Value = s

End Sub


Marko

 

[broro183]

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
moved (eg a cell or range etc).
IMHO, a better solution would involve:
*Firstly, identifying what is causing the "#REF!" error.
A possible cause of this is that there is a reference in the copied
cells to an area in the place where you copy the cells to - when the
cells are pasted over, any formula that referred to the covered area
will include "#REF!" rather than a cell reference (eg B3). If this is
the case, your approach could be providing incorrect data.

*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/Applications/MS_Office/Q_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

 

[Robert McCurdy]

Hi Ben

Selection.copy [H2]
Selection.ClearContents

The above just moves a selection to H2.
Have you tried moving the entire column or row by holding the Shift button down, click-and-drag the selected cells with the mouse to
where you want?


Regards
Robert McCurdy

Hello,

I would like to imitate a cut and paste which would do a copy instead of a
cut and a paste and then delete of the original selection instead of the
regular paste (i.e I would do copy, paste, delete instead of cut and paste).
By recording a macro I could see how the copy and paste are done but I don't
know how I would get a second function to remember where the original
selection was in the first function to do a delete on it after the paste.

I am wanting to do this because in a spreadsheet I have, when I do a cut and
paste it turns many cells into "REF!" and I get around it by doing a copy,
paste and delete. So I was thinking of getting around doing this each time
by combining the paste and delete part. Any help would be appreciated.

Thanks,
Ben

 

[ben]

Hello. Thanks. But the idea is to highlight the cells I need to move then
cut them (ctrl-del) and then paste where I want (shift-ins). So I would tie
one function to the ctrl-del shortcut which would do the copy and store the
range that needs to be copied somehow. Then on shift-ins another function
would paste whatever was copied and clear the contents of the original
cells. When I created a macro to see what happens when I do it by hand I
noticed that it selects the area I am going to paste to which I presume
means I lose the original selections and can't clear its contents afterwards
so I need to remember where it was.

 

[ben]

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

 

[broro183]

Hi Ben,
I'm only learning too so if there is someone with a better suggestion,
*please speak up*.

Yes, you are right, excel is trying to adjust the formulae but "A
display of #REF! means that your formula refers (directly or
indirectly) to a cell that no longer exists, due to a change in the
worksheet/workbook/other precedents."
In your case, the cutting & pasting you are doing changes the worksheet
structure & therefore your formulae *will not work*/be accurate - as you
have shown with your example. I'd recommend copying the "if" formula
down from the top row of all your status columns so you know it is
referencing the correct cells. I suspect that any cutting you have done
in the past will have thrown some of the formulae out of sync so that
even some that work are not referring to the correct row. This can be
easily checked by selecting a cell in column E & pressing F2 - the
cells that are being referenced are usually highlighted when this is
done.

I still question the need for cutting & moving the data around b/c I
assume it is being moved due to a change in the "status" column. If
this is the case, have you considered using >data - sort, & rearranging
the rows based on column E?
However, I've had a play & modified the below for you. It isn't the
"ideal" that you requested but may be better (?) as it includes both "a
copy + stores the range and then pastes & delete/clears the stored range
from earlier". It appears to work but I haven't been thorough with error
testing/trapping.
NB, this macro deletes the complete rows which your copy range was on
to prevent the "#ref!" problem. However, if you have more information
in column F & further right, remove the 2 lines ending with '* &
replace with line1 "Range(MyAddr).Delete Shift:=xlUp" and line2
"range("e2").copy Range("e2", Selection.End(xlDown))".


Sub CopyPasteAndDeleteOriginalRange()
'sourced (& modified) 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 PasteRng = Application.InputBox("Select range", sDlgTitle,
ActiveCell.Address, Type:=8)
MyPasteAddr = PasteRng.Address
If URng Is Nothing Then Exit Sub
'COPY, PASTE, & DELETE CODE HERE USING THE RANGE DEFINED BY YOUR
SELECTION.
Range(MyAddr).Copy Range(MyPasteAddr)
Range(MyAddr).EntireRow.Select '*
Selection.Delete Shift:=xlUp '*
MsgBox "Done"
End Sub

Hth
Rob Brockett
NZ
Still learning & the best way to learn is to experience...