Excel Macro for paste values as transpose

Anu G
Anu G used Ask the Experts™
on
I need values from column M82 : T82 to be pasted in the row L83 : L90. I have large amount of data to be pasted in this way. I tried some macros but tis not working.Can anyone help me?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
Dim X() As Variant
Dim y() As Variant
X = ActiveSheet.Range("M82:T82").Value2
y = Application.Transpose(X)
Top Expert 2016
Commented:
Hi,

pls try
    Range("M82:T82").Copy
    Range("L83").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

Open in new window

Regards
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Since you are trying to paste values, try this.....

Range("M82:T82").Copy
Range("L83").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = 0

Open in new window

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Rob HensonFinance Analyst

Commented:
I have large amount of data

M82 : T82 is only 8 cells, not exactly a large amount of data. Please expand on how this is to be used for a large amount of data.

Thanks
Rob H
Glenn RayExcel VBA Developer
Top Expert 2014
Commented:
^I agree with Rob.  Your example seems easy enough to just do manually, but I presume you want to be able to repeat this function quickly rather than use existing shortcut key sequences.

In Excel 2010-2013, after copying, the Alt-key sequence is just: [Alt],H,V,S,V,E

However, if you're wanting to get it down to one keystroke combination, add the following code to your workbook (or Personal workbook to make it global) and then assign a shortcut key combination using the Macro Options:
EE-AssignShortcutKeys2.jpg
This code will transpose ANY copied range, anchored at the active cell.
Sub PasteTranspose()
    ActiveCell.PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
All the chosen answers addressed the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial