Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
could this hepl:
Sub Button1_Click() Dim adr As String Range("a1:c15").Select adr = Selection.Address Range("b2").Value = s End Sub Marko "ben" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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/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 -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=499117 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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 "ben" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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. marko wrote: could this hepl: Sub Button1_Click() Dim adr As String Range("a1:c15").Select adr = Selection.Address Range("b2").Value = s End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
imitating cut and paste
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=499117 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
How do I capture user paste action and convert to Paste Special | Excel Programming | |||
Imitating Pivot table's context awareness | Excel Programming |