Paste Value Transpose Shortcut?

[Artorius]

I use the Paste Value shortcut button that I've added to my toolbar in Excel. Just as regularly, I also need to do a Paste Special, Values Only with the Transpose option selected. Is there a way to create my own shortcut icon and build code to do this?

 

[Datsmart]

Open the VBA Editor, (Alt-F11). The left side panel called Project Explorer, (Ctrl-R if it is not there), should have a VBA project called "Personal.xls". If not, follow the steps in my earlier post to have Excel create one.
You can "drag" any module between projects to create a copy of all the macros within that mocule. Just drag the module your code is in into the personal.xls project. Assign your button or icon to use the code in "personal.xls" instead of the macro you first set it up with. Now any Excel session can use your code, no matter what file is open.

 

[1968rm]

The problem with a macro, is that you can't undo.

 

[icoffice]

How do I do this in Office 2013 of Excel? Thanks!

 

[charisma1312]

I used this thread to set up a macro and then associated shortcut in the Quick Access Toolbar. Then I was forced to do a quick repair on MS Office, and my macro stopped working. I have been trying to set it up again the same way I did before (create macro stored in PERSONAL.XLSB, add to Quick Access Toolbar, etc.), but I can't even get the macro created successfully. I used the steps exactly as they are outlined in this thread the first time I set it up, and no matter what I do (use code in this thread, create the macro from scratch, I've tried everything), I keep getting an error. It is Run-time error '1004': PasteSpecial method of Range class failed. What is that??? I can't figure out what I am doing wrong. I had this set up and used it for months and it was perfect, and now ... all I get is an error. When I click to debug, the yellow highlighting is all around this (in this example, it is a macro I tried to re-record to start from scratch). Can anyone help me figure out what I am doing wrong? I get the same error if I use the code (in the PERSONAL.XLSB) as listed earlier in this thread. Same error, same highlighted text in debugger.

 

[Fluff]

Have you copied anything to begin with?

 

[charisma1312]

Have you copied anything to begin with?

Yes. When I was trying to record a macro myself to move to the PERSONAL.XLSM, here are my steps:

1. Ctrl + C to copy something
2. Press "Record Macro"
3. Go through menu to Paste Special, Paste Values checkbox, Transpose checkbox, OK.
4. The text was pasted
5. Stop Recording the macro

Then to run the macro, I did:

1. Ctrl + C to copy something
2. Run Macro

Then I got the error over and over. I even got the same error if I copied and attempted the same text and positioning I used to record the macro. I did some online searching and it seems like some people are having an issue with PasteSpecial in general, but I do not know VBA at all so I don't know how I got it to work the first time, and now it won't.

I will also note that I ran a full repair on Office yesterday, rebooted, and tried all of this again, with the same results.

 

[charisma1312]

(* Move to the PERSONAL.XLSB* not .XLSM)

 

[Fluff]

What was the range you copied?
and what cell/s were selected when you ran the code?

 

[charisma1312]

Text copied: A2 to A10
When recording macro, pasted to C2 to K2 and then one more time Use Relative References in C4 to K4.

Record macro...

Results after recording the macros ... again, one with absolute values and one with relative. The macro code for both ...

Then I try running it. I tried running the Macro3 first, and got the Runtime Error 1004 as I described above. Then I got an interesting error when running Macro2. I placed my cursor in C2 to see if that made a difference, and I got this:

I need the macro to use relative cell references so it will work no matter what worksheet(s) I'm working on, and that is how it was before. I have no idea what "break mode" is.

 

[Fluff]

That error means that macro3 was still running (ie you hadn't cancelled it after the error)
However I don't know why you are getting the 1004 error.