VBA The 'cut' equivalent of 'copy and paste special transposed formulas'

i_excel

Board Regular
Joined
Jun 4, 2015
Messages
113
Hi

I am wondering how easy the following can be done with VBA. I would like to paste special formulas that are already cut and on the clipboard, into the transposed array defined by the active cell.

In essentials I am looking for the 'cut' equivalent of paste special transpose formulas. Any help would be much appreciated.

Kind regards
i_excel
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
If it is of any help - here is the code that I have tried and which doesn't work:

Sub cptf2()


ActiveCell.Select
ActiveSheet.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True


End Sub
 
Upvote 0
In the event that there are interested observers monitoring this thread, I should report the findings of my research. It seems that excel does not allow the pastespecial function to be coupled with cut. I considered other approaches but to no avail.

i_excel
 
Upvote 0
Best option is to Copy / Pastespecial....Then clear the original cell
 
Upvote 0
Hi Michael

Thank you for your reply. Unless I'm mistaken, the approach you suggest necessitates isolating formulas (e.g. removal of =) before transposition. But I think that it may still be the best of an imperfect lot of substitutes.

i_excel
 
Upvote 0
Depending on the formulae, maybe not.
If you copy a formula with VBA, and then Paste special as the next line, it shouldn't affect the formula itself.
Then when the code in the source sheet is cleared, there is no affect on the Pasted cell

Try it !!
 
Upvote 0
Hi Michael

Apologies for the delay in getting back to you. I have tried to use it as you suggest, but without luck. I guess that your code is more sophisticated than mine. With the code I use, I can copy cells from a range and have them transposed to another, but the formulae don't carry through. Here is my code in case you can spot some obvious mistakes, it results in #REF! errors:

Sub cutcopy()


Range("b10:b11").Copy
ActiveCell.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, Skipblanks:= _
False, Transpose:=True


End Sub

Kind regards
i_excel
 
Upvote 0
HAve a look here for the correct method AND an explanation on how / why !!
It's not quite as simple with formulas as with plain old data !

Transpose Table
 
Upvote 0
OK - woah. Now I am using VBA. Thank you Michael for bearing with me, that is nifty. I will explore it at greater length, but from initial use I can confirm that it keeps the formulae intact when transposing them.

Kind regards

i_excel
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top