Macro for paste values and transpose

straggleyway

New Member
Joined
Mar 1, 2009
Messages
5
Hi all,

This is driving me crazy. Really grateful for help. I want to make a macro that I can copy a column of numbers, select a cell... then run the macro - which will transpose them and paste the values.


I've tried to make this by Recorder... but I keep getting an error.

Run-time error 1004
Paste-Special method of Range class failed.

I've seen other people in the past post this questions, but it seems noone has solved it!

Any ideas? Code below....

Thank you!!


Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

shyrath

Active Member
Joined
Jun 28, 2005
Messages
483
Try this

Will ask you to select the range you want to transpose.
Then will ask you to select the destination.
Then will complete the transpose.

Code:
Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
'
'copy the selected range

On Error Resume Next
 
'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(Prompt:="Block input cells/range", _
Title:="Copy Paste", Type:=8)
 
'Show input box to get where they want it paste
Set OutputCells = _
Application.InputBox(Prompt:="Select cell where you want paste it", _
Title:="Copy Paste", Type:=8)
 
'Copy range of input cells
InputCells.Copy
 
'Paste it into output cells reference

OutputCells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Application.CutCopyMode = False

End Sub
 

DanD

Board Regular
Joined
Dec 23, 2008
Messages
124
Can you post you're full code?

I can't find any errors with it, just used the following and it works fine

Code:
   Range("A2:A4").Select
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Application.CutCopyMode = False
 

straggleyway

New Member
Joined
Mar 1, 2009
Messages
5
Hi Dan,

yes, I get that to work too. The problem is when I remove...

Range("A2:A4").Select
Selection.Copy
Range("D2").Select


from the example you pasted. The above code specifies A2 to A4. I want a general macro.... ie I copy any column, select a cell and THEN run the macro with the data already copied to the clipboard.

When you remove these lines.... it doesnt work and I get the error.

thanks!!
 

straggleyway

New Member
Joined
Mar 1, 2009
Messages
5

ADVERTISEMENT

sorry, the code im using is this.... made by using the Recorder and then removing the lines that reference *specific* cells.



Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub
 

straggleyway

New Member
Joined
Mar 1, 2009
Messages
5

ADVERTISEMENT

Hi Shyrath,

thanks for your code - so am I right thinking that you cant have a macro to do what Im aiming at? ie to copy a column of numbers, select a cell ....THEN run macro which will transpose and paste only values?
 

shyrath

Active Member
Joined
Jun 28, 2005
Messages
483
Hi Shyrath,

thanks for your code - so am I right thinking that you cant have a macro to do what Im aiming at? ie to copy a column of numbers, select a cell ....THEN run macro which will transpose and paste only values?

Sure it will, your piece of code works fine for me , as long as i follow this:

1. Select a range of cells
2. Copy that range
3. Select the destination CELL (not a range)
4. Run the macro

the version i posted above, does the same, just asked you for step 1, then does step 2, asks you for step 3 and then does 4
 

straggleyway

New Member
Joined
Mar 1, 2009
Messages
5
Hi Shyrath,

The steps you highlight is exactly what i'm doing..... but i still get the run-time error 1004. PasteSpecial method of range class failed.

thanks for all your time and help I'll just have to use the old-fashioned way.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top