[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Excel Macro for paste values as transpose

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
Anu G
Asked:
Anu G
4 Solutions
 
ProfessorJimJamCommented:
Dim X() As Variant
Dim y() As Variant
X = ActiveSheet.Range("M82:T82").Value2
y = Application.Transpose(X)
0
 
Rgonzo1971Commented:
Hi,

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

Open in new window

Regards
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
^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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
All the chosen answers addressed the issue.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now