Code works when running via editor but not when clicking hyperlink

Question

The purpose of my macro is to simply take some information from one sheet and transfer it to another to prevent having to re-enter information. The code works perfectly when I run it via the VBA editor but results in in a Run-time error '1004': Applicaiton-defined or object-defined error when I try to run it via the hyperlink. I know the hyperlink is linked to the correct macro. What's going on?

Sub Insert_PCO_Row()

    ' Insert_PCO_Row Macro
    ' Inserts PCO information into COR log if COR number is entered in COR number column in "Sub Pricing" Worksheet.

    Dim corNum As Range
    Dim nextOpen As Range

    Sheets("Sub Pricing").Select
    Range("C3").Select

    Set corNum = Sheet6.Range("A1:A1000")

    Do Until Selection.Offset(0, -1) = ""
    'Checks if COR # is entered in "Sub Pricing" tab OR if the COR # is already entered in "COR Log" tab.
    If Selection.Value = "" Or Application.WorksheetFunction.CountIf(corNum, Selection.Value) > 0 = True Then
        Selection.Offset(1, 0).Select
    Else
        Set nextOpen = Sheet6.Range("A9").End(xlDown).Offset(1, 0)
        Selection.Copy
            nextOpen.PasteSpecial xlPasteValues
        Selection.Offset(0, 1).Copy
            nextOpen.Offset(0, 1).PasteSpecial xlPasteValues
        Selection.Offset(0, -2).Copy
            nextOpen.Offset(0, 2).PasteSpecial xlPasteValues
        Selection.Offset(0, -1).Copy
            nextOpen.Offset(0, 3).PasteSpecial xlPasteValues
        Selection.Offset(0, 7).Copy
            nextOpen.Offset(0, 7).PasteSpecial xlPasteValues
        Selection.Offset(1, 0).Select
    End If

    Loop

    Sheets("COR Log").Select

End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Try it without using .Select.

Option Explicit

Sub Insert_PCO_Row()
    ' Insert_PCO_Row Macro
    ' Inserts PCO information into COR log if COR number is entered in COR number column in "Sub Pricing" Worksheet.

    Dim rw As Long, nrw As Long

    With Worksheets("Sub Pricing")
        For rw = 3 To .Cells(Rows.Count, 2).End(xlUp).Row
            With .Cells(rw, 3)
                If CBool(Len(.Value2)) And _
                   Not IsError(Application.Match(.Value2, sheet6.Columns(1), 0)) Then
                    nrw = sheet6.Cells(Rows.Count, "A").End(xlUp).Row + 1
                        sheet6.Cells(nrw, 1) = .Value
                        sheet6.Cells(nrw, 2) = .Offset(0, 1).Value
                        sheet6.Cells(nrw, 3) = .Offset(0, -2).Value
                        sheet6.Cells(nrw, 4) = .Offset(0, -1).Value
                        sheet6.Cells(nrw, 8) = .Offset(0, 7).Value
                End If
            End With
        Next rw
    End With

    Worksheets("COR Log").Select

End Sub

Using the Range .Select method and relying on the Application.Selection and ActiveCell properties to identify the source and target of your operation is simply not reliable. In a similar vein, direct value transfer is more efficient than a Copy/PasteSpecial, Values operation and does not involve the clipboard.

Thank you, Jeeped. The direct value transfer method is much cleaner than my original copy paste method. I appreciate the quick feedback. — Matt Ginn, Apr 19 '16 at 16:40

Code works when running via editor but not when clicking hyperlink

1 Answers1