PasteSpecial Method of Range Class Failed

[SBF12345]

I would like to be able to take data presented along a single row and have it moved to a single column. The code below is what I have constructed in order to accomplish this task. I am receiving an error "Pastespecial method of range class failed" on the line immediately below. Why is it doing this?

 ActiveCell.Offset(1, -1).PasteSpecial Paste:=xlPasteValues

   Dim A As Integer    Dim B As Integer


'
    ActiveCell.Activate
    
    A = ActiveCell.End(xlToRight).Column
    
    For B = 0 To A
    
        ActiveCell.Offset(0, 1).Select
    
        ActiveWorkbook.ActiveSheet.Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.End(xlToRight).Column)).Cut
    
        ActiveCell.Offset(1, -1).PasteSpecial Paste:=xlPasteValues
        
        ActiveCell.Activate
    
    Next B
    
End Sub

 

[smozgur]

Try this code:

Public Sub DoIt()
    With ActiveSheet.Range(ActiveCell, ActiveCell.End(xlToRight))
        .Copy
        .Offset(1, -1).Resize(, 1).PasteSpecial Transpose:=True
        .ClearContents
    End With
End Sub

• It first selects the range in the row (starting from the active cell, to the last column with data - note that if you have blank cells in between, then it will not work right).
• With ... End With helps to work with the same object reference in this sample.
• It then copies the range, runs PasteSpecial on the one row below of the previous column with Transpose option, so it pastes row range as column. We don't want to use Cut method but Copy because we need to use PasteSpecial.
• Then while we are still in With ... End With, ClearContents clears the original source range (Cut effect).

We just need the ActiveCell object to define the starting point in this code, then we use Offset and Resize methods to define the source and target ranges instead activating and processing each cell.