2

Is it possible to make this paste option (paste only values with transpose at the same time). As it shown on the schreenshot:

paste only values with transpose

and having one button for this option in drop-down menu here:

excel drop-down menu

PeterH
  • 7,377
  • 20
  • 54
  • 82
XuMuK
  • 300
  • 4
  • 15
  • 2
    you could create a macro that would do it in your personal workbook, then create a button for it in the ribbon that fires that macro – PeterH Oct 16 '17 at 11:54
  • @User91504 Yes, I could, but I have zero experience (or only negative) with MS office macros. And this will be working only in one document or it is possible to make working in all excel documents? – XuMuK Oct 16 '17 at 14:20
  • yeah you can do it accross any work sheet, i dont havfe excel 2016 so i cant make the macro for you, but google how to record a macro, you dont need to write it – PeterH Oct 16 '17 at 14:21
  • @User91504 yes! it is much easier than I have experienced before (and also it is working). – XuMuK Oct 16 '17 at 15:15

1 Answers1

3

Thanks User91504, for encouraging me to try MS macrosing.

The working solution has very simple code:

Sub Paste_transpose()
'
' Paste_transpose Macro
'

'
    ActiveCell.Offset.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
End Sub

And there is possibility to add keys combination for this macro - no need to add any buttons anywhere.

XuMuK
  • 300
  • 4
  • 15
  • 1
    AWESOME stuff ! also check out these 2 links https://support.office.com/en-gb/article/Create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790 http://www.dummies.com/software/microsoft-office/excel/how-to-assign-macros-to-the-ribbon-and-the-quick-access-toolbar-in-excel-2013/ – PeterH Oct 16 '17 at 16:12
  • @User91504 Thanks! Now it will be working in all my computers. And using keyboard shortcut is much faster. At the beginning I thought there is possibility to customize paste options to make favorite. – XuMuK Oct 16 '17 at 18:13