CODEWITHSUNDEEP
excel
Delinda Dyta
Delinda Dyta

Reputation: 207

Error 1004: PasteSpecial method of range class failed

I am having a little bit trouble, I want to copy one column from another workbooks into one new workbook and transpose it, but I've got an error like : Error 1004: PasteSpecial method of range class failed

Private Sub CommandButton1_Click()

    ActiveSheet.Range("C2:C30").Copy
    Workbooks.Open Filename:="E:\PENDIDIKAN_BJN\SUM.xlsx"
    eColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    If eColumn >= 1 Then eColumn = eColumn + 1
    ActiveSheet.Cells(1, eColumn).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False

End Sub

Upvotes: 2

Views: 3773

Answers (1)

Pierre Chevallier
Pierre Chevallier

Reputation: 754

Reason of the issue

You are trying to copy the cell C2 to C30, so 29 cells (30-2+1) to the other workbook by transposing them. In other words you paste these cells as a single row with 29 columns.

In your other workbook you select "dynamically" your columns with If eColumn >= 1 Then eColumn = eColumn + 1 and so you are not certain to have the selection of 29 columns required to do the paste.

The result in the end is the error message Error 1004: PasteSpecial method of range class failed.

Solution

One of the solutions would be to select directly the right number of columns to paste the data and do so like this:

Private Sub CommandButton1_Click()

    ActiveSheet.Range("C2:C30").Copy
    ' Store the number of rows selected in the eColumn associated before opening the other file
    ' Since this is a transposition the number of rows is the same as the target number of columns
    eColumn = Selection.Rows.Count

    Workbooks.Open Filename:="E:\PENDIDIKAN_BJN\SUM.xlsx"
    ActiveSheet.Cells(1, eColumn).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False

End Sub

Upvotes: 1

Related Questions