In 2002, the Paste special option is available only after copy, but not after cut.
Can we write a macro that would remedy that issue, i.e show the paste special dialog after cut?
Also as a branch off the above, I would like a macro that would just paste / transpose instead of showing the dialog. (and of course clear the cut section).
Thanks
Excel 2002 is rather old, but this might work. I mapped the macro PasteAsSpecial to CRTL-SHIFT-V. This macro launches a form that looks a lot like the paste dialog box.
So copy what you want. Select the range where you want to paste it. Press CTRL-SHIFT-V and check the boxes for the paste specials that you want. Click on Paste.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Could you please convert to .xls
Thanks
I will do that. I need to test it again. It seems to have stopped working!
Thank you thumbs-up-192.png
Why not just use the Copy, then PasteSpecial, and immediately follow it up with clearing the original.
Example:
Please Login or Register to view this content.
HTH
Regards, Jeff
Because A1 wont always be the source (it also might be a range) and A2 wont always be the target,
if you modify it, I guess it will do.
(Select range to be ultimately cut - select target - run macro that will do the paste and clear the source range).
Update please.....
@jeffreybrown
Could you update this thread please!
Update it to what?
Last edited by jeffreybrown; 05-28-2017 at 10:53 PM.
See post 7 please!
edit:
run time error 1004: pastespecial method of range class failed.
on Cut_Paste(1).xls
Please edit post 6 as per post 7.
Last edited by drgkt; 05-28-2017 at 11:51 PM.
What I have is basically a copy / paste. I'm still trying to figure out a way to go back and remove the original selection. I don't want to have to put a selection change event on every sheet.
The "go to" part is easy: it's where you select to paste. The "come from" part might be logically impossible with VBA. Obviously something inside Excel knows since it can do a cut, but whatever slight of hand is used may be hidden from us mere mortals.
What about working on the code in post 6?
It seems simpler, just needs to be modified per post 7.
Here is the issue with the code
OrPlease Login or Register to view this content.
Please Login or Register to view this content.
I see your point.
How about this?
Run macro:
1. Asks to choose the cell / range to be cut.
2. Asks to choose the target cell.
3. Presents the Special Paste dialog box.
4. Performs what is chosen in the dialog box.
5. Clears the selection in step 1.
I took another shot at this. What I suggest you do is download both the module and the form so you can import them into whatever workbooks will need them. The downloaded module contains the control key mappings.
To use the program, select what you want to cut and press CTRL-SHIFT-X. Then select to where you want to paste it and press CTRL-SHIFT-V - this launches an Excel form that looks like the paste special form for copy. The only thing missing is Paste Link. I had it in there (and the code still exists) but when I tested it, I got zero. That's logical, I am pointing to a cell whose contents just got cut. So it doesn't make sense to have this feature there.
Otherwise the paste works just like the paste on a copy would work - at least as far as I've tested it.
It does not work here.
Remember I am using Excel for XP.
The file loaded, it got converted but CTRL-SHIFT-X and CTRL-SHIFT-V do not work.
If I import this into PERSONAL.XLS that loads every time Excel is started, will I be able to use it on any workbook?
Here is a shot of what the paste special dialog box looks like in Excel for XP:
I can't speak for Excel XP I skipped from 97 to 2000 to 2003 to 2007 to 2013. I never had XP.
Including it in the PERSONAL.XLS is a better idea than what I suggested. You may have to map CTRL-SHIFT-X to the macro SpecialCut and CTRL-SHIFT-V to the macro SpecialPaste manually using macro options.
The form that comes up on CTRL-SHIFT-V looks almost like the one you show, except I also have All Using Source Theme and All merging Conditional Formats - these options probably will not work with XP.
I followed your advice regarding mapping.
Hitting CTRL-SHIFT-X does not have the effect of the dashed border around the selection as with the regular CTRL-X, but that is minor.
Hitting CTRL-SHIFT-V gives the following error when selecting VALUES.
I'll have to take those two options out of both the code and the form.
Oh, well!
I thought it might be a simple task as per post 15.
Don't consume yourself over it.
Try this version.
If you want to transpose something like this might work:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Copied the code into the PERSONAL.XLS.
Seems to be working. Any observations I will report in time.
Thanks a lot!
Great glad it works - yes, please let me know
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks