Range(...).PasteSpecial xlPasteComments yields error 1004: PasteSpecial method of Range class failed RRS feed

  • Question

  • I have a workbook with extensive VBA automation. In one operation, I am copying and pasting data from one workbook to another. After making some changes, I am now getting the Error "1004: PasteSpecial method of Range class failed". This is a very long post but I would be VERY appreciative if someone is willing to look at this.

    I have tried this on Office 2010 and 2007.

    The error is occurring on the following line of code:

    ActiveSheet.Range(taskMap(taskIndx)(3)).PasteSpecial xlPasteComments

    The really puzzling aspect is that this only fails after the code has inserted rows into the target sheet. Here is more of the code and what it's doing.

    This is a process where I have a macro enabled template file. And users create working macro enabled workbooks from the template. If I update the template for fixes or updates, I have a process where the user can click a button and the VBA code will copy all of their data from the old workbook to a new one. The template file has a template worksheet and for each sheet in the users file the template sheet is copied to working sheets and their data is copied to the sheet. If the user's sheet has more data rows than the template sheet, then I insert the needed rows into the new sheet.

    This was working fine until I decided I needed a more flexible insert rows function. So I'm trying to figure out what the new insert rows function is doing that the paste special doesn't like.

    Here's a fragment from the routine that is cycling through the users worksheets and copying their data.

    ' Cycle through all the worksheets in the old file and copy the data to the new workbook.
    For Each WS In Workbooks(wbOldName).Worksheets
        If WS.Name = wsListsName Or WS.Name = wsTemplName Then 'ignore the template and lists sheets
        ElseIf WS.Range("C3") <> wsNameID Then                 'We can only work on ws created from the template sheet
            MsgBox "A worksheet was found that was not a budget worksheet." & vbCrLf & _
                   "The worksheet " & WS.Name & " can not be migrated to the new workbook.", Title:="Migrate Workbook"
            ' copy template ws to new ws
            Sheets(wsTemplName).Copy after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = WS.Name

            ' Find the last data row
            BudgetDataLastRow = WS.Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
            'if Expenditure Table has more rows than the template then expand the table.
            With Range("Bud_ExpenditureTable")
                If BudgetDataLastRow > .Areas(ExpLowerRight).Row - 1 Then

                    Set currRange = .Areas(ExpLowerRight).Offset(-1, -1)
                    rowInsertCnt = BudgetDataLastRow - .Areas(ExpLowerRight).Row + 1

                    InsertRows currRange, rowInsertCnt, False   '<<< New insert rows function

    '                ExpandExpTable BudgetDataLastRow           '<<< Old insert rows function

                End If
            End With

            ' Cycle through the pre-determined regions of the worksheet to be copied to the new sheet
            For taskIndx = 0 To UBound(taskMap)

                Select Case taskMap(taskIndx)(0)
                Case 1 ' Copy and paste range with fixed number of columns and variable number of rows.
    '               Debug.Print "Var Range: ", taskMap(taskIndx)(1)
                    With WS
                        ' if the budget table has data, then copy it.
                        If BudgetDataLastRow >= Range("Bud_ExpenditureTable").Areas(ExpUpperLeft).Row + 1 Then
                            .Range(taskMap(taskIndx)(1), _
                                .Cells(BudgetDataLastRow, taskMap(taskIndx)(2))).Copy
                            ActiveSheet.Range(taskMap(taskIndx)(3)).PasteSpecial xlPasteValues
    Code Fails here>>>      ActiveSheet.Range(taskMap(taskIndx)(3)).PasteSpecial xlPasteComments
                        End If
                    End With

    The taskMap array contains cell references that guide the process and tell the Select/Case statement how and where to copy the data. As you see, I also use Named Ranges to identify the areas to be copied.

    Here are the two subs that expand the table:

    Sub ExpandExpTable(LastDataRow As Long)

    Dim tblFirstRow As Long
    Dim tblLastRow As Long
    Dim tblSize As Long
    Dim InsertCnt As Long

    With Range("Bud_ExpenditureTable")
        tblFirstRow = .Areas(ExpUpperLeft).Row + 1
        tblLastRow = .Areas(ExpLowerRight).Row - 1
    End With

        InsertCnt = LastDataRow - tblLastRow
        While InsertCnt > 0
            tblSize = tblLastRow - tblFirstRow
            If InsertCnt > tblSize Then
                Rows(tblFirstRow + 1).Resize(tblSize - 1).Select
                Rows(tblFirstRow + 1).Resize(InsertCnt + 5).Select
            End If
            Selection.Insert Shift:=xlDown

            tblLastRow = Range("Bud_ExpenditureTable").Areas(ExpLowerRight).Row
            InsertCnt = LastDataRow - tblLastRow + 1

    End Sub

    Sub InsertRows(CurrCell As Range, rowCnt As Long, fillBudgetTable As Boolean)
    Dim n As Long, k As Long
    Dim PrevCell As Range
    Dim indx As Long

        With Application
    '        .ScreenUpdating = False
    '        .EnableEvents = False
        End With

        If rowCnt = 0 Then Exit Sub
        ' Allow us to put the user back to the selected cell.
        Set PrevCell = ActiveCell
        Range(CurrCell, ActiveCell.Offset(rowCnt - 1, 0)).EntireRow.Insert
        'need To know how many formulas To copy down.
        k = ActiveCell.Offset(-1, 0).Row
        ' Special range with areas is used to determine which columns to fill.
        If fillBudgetTable Then
            With Range("Bud_fillareas")
                For indx = .Areas.Count To 1 Step -1
                    Range(Cells(k, .Areas(indx).Column), Cells(k + rowCnt, .Areas(indx).Column + .Areas(indx).Columns.Count)).Select
                Next indx
            End With
        End If

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With

    End Sub

    As you see, the first one selects up to 5 rows, then does the insert, while the second just selects the row where the insert will occur and then does the insert. Both seem to accomplish the insert as expected. I want to use the new insertRows because it is more general and is needed for other ws functions I've built. I have checked the table formatting, and other Cell parameters and haven't found anything. I also played with the 'Shift' parameter. Tried to select just the currCell. All no different.

    Any thoughts or things to try are welcome.

    Saturday, April 21, 2012 2:24 AM

All replies

  • Can you upload one template and one file after user edit.It is difficult to recreate the situation.
    Saturday, April 21, 2012 5:24 AM
  • It might be difficult to send a file that fails because it may have something to do with the data specifically in the file I'm using as my test file. It has internal data that I probably shouldn't send out. I'll think about if I can sanitize the data so I can send it.

    After doing some testing and trying some different things, it appears that the failure is much more subtle than I thought. Here's what I've found:

    1. Something seems to be happening to the data in the clipboard. If I add a second cmd to copy the data, it doesn't fail:

                        ' if the budget table has data, then copy it.
                        If BudgetDataLastRow >= Range("Bud_ExpenditureTable").Areas(ExpUpperLeft).Row + 1 Then
                            .Range(taskMap(taskIndx)(1), _
                                .Cells(BudgetDataLastRow, taskMap(taskIndx)(2))).Copy
                            ActiveSheet.Range(taskMap(taskIndx)(3)).PasteSpecial xlPasteValues
                            .Range(taskMap(taskIndx)(1), _
                                .Cells(BudgetDataLastRow, taskMap(taskIndx)(2))).Copy
    Code Fails here>>>      ActiveSheet.Range(taskMap(taskIndx)(3)).PasteSpecial xlPasteComments
                        End If
    2. Once I make the template with this code I can fully build the new file with all the users data. I only copy the data values and comments. I leave all other formatting and cell data behind. Once I do that, and run the process again without the second copy in the code, it doesn't fail. That means whatever was wrong was left behind in the old file.

    Well, As I write this and test, it appears the failure has disappeared! I did some code clean up, like removed some redundant .ScreenUpdating and .EnableEvents calls. And now I can't get it to fail, even with going back to the original copy of the user file I was working with. I have no idea how the stuff I removed could influence this.

    Oh well, thanks for looking.

    John Ed

    Saturday, April 21, 2012 7:51 AM