Solved

Excel Macro for paste values as transpose

Posted on 2016-09-16
6
77 Views
Last Modified: 2016-10-05
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?
0
Comment
Question by:Anu G
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points (awarded by participants)
ID: 41801267
Dim X() As Variant
Dim y() As Variant
X = ActiveSheet.Range("M82:T82").Value2
y = Application.Transpose(X)
0
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points (awarded by participants)
ID: 41801318
Hi,

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

Open in new window

Regards
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 125 total points (awarded by participants)
ID: 41801362
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

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41801500
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
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 125 total points (awarded by participants)
ID: 41802405
^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

0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41829540
All the chosen answers addressed the issue.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question