Solved

Excel Macro for paste values as transpose

Posted on 2016-09-16
6
38 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
6 Comments
 
LVL 25

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 49

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 29

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 32

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 29

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now