You cannot copy paste special values and transpose on the same area.

I suggest one solution as follows:

insert a temp worksheet
cut the original area
paste special values on the new temp sheet
copy the pasted values
paste special transpose on the original worksheet
delete the temp worksheet

as in

Option Explicit

Sub copypastespecialvaluestranspose()

Dim wso As Worksheet, _
wst As Worksheet
Dim R As Range
Set wso = ActiveSheet
Set wst = ActiveWorkbook.Worksheets.Add
wso.Activate
'szR = Selection.Cells(1, 1).Address
Set R = Selection.Cells(1, 1)
Selection.Copy
wst.Activate
wst.Cells(1, 1).PasteSpecial _
xlPasteValuesAndNumberFormats, , , True
wso.Activate
Selection.Clear
R.Select
wst.UsedRange.Copy R
Application.DisplayAlerts = False
wst.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"swaroop" wrote:

Hi All,
I have a set of values in worksheet.
I want to transpose the data in the worksheet.
I want to copy all cells and Paste Special on top of the existing cells with
the trasposed data.
How can i copy all the cells in worksheet and then use Paste Special.

Any pointers are greatly appreciated.

Thanks,
Swaroop

P.S: I tried copying a range of cells and worksheet and when i try to paste
the cells by giving a Range , i get an COMException ,that copy and paste
areas should be the same.

Thanks all for responses.
psuedo code for what i have done is as follows.

a. Selected Range as i know the number of rows and columns.
b. Copied the data from Range.
c. Pasted the data into another region of worksheet with Transponse
d. Delete the Original data.

Let me know if this is a optimal solution.

"Martin Fishlock" wrote:

You cannot copy paste special values and transpose on the same area.

I suggest one solution as follows:

insert a temp worksheet
cut the original area
paste special values on the new temp sheet
copy the pasted values
paste special transpose on the original worksheet
delete the temp worksheet

as in

Option Explicit

Sub copypastespecialvaluestranspose()

Dim wso As Worksheet, _
wst As Worksheet
Dim R As Range
Set wso = ActiveSheet
Set wst = ActiveWorkbook.Worksheets.Add
wso.Activate
'szR = Selection.Cells(1, 1).Address
Set R = Selection.Cells(1, 1)
Selection.Copy
wst.Activate
wst.Cells(1, 1).PasteSpecial _
xlPasteValuesAndNumberFormats, , , True
wso.Activate
Selection.Clear
R.Select
wst.UsedRange.Copy R
Application.DisplayAlerts = False
wst.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"swaroop" wrote:

Hi All,
I have a set of values in worksheet.
I want to transpose the data in the worksheet.
I want to copy all cells and Paste Special on top of the existing cells with
the trasposed data.
How can i copy all the cells in worksheet and then use Paste Special.

Any pointers are greatly appreciated.

Thanks,
Swaroop

P.S: I tried copying a range of cells and worksheet and when i try to paste
the cells by giving a Range , i get an COMException ,that copy and paste
areas should be the same.