.Paste works in 'If' portion of statement, but not 'Else'

Question

I'm automating a process to import data from a worksheet, though for data integrity (and to eventually append to a database), I do not want the identifier entered twice. My code works to import the data if the identifier (SHC_No) is not in column A, and to prompt if you would like to replace the entries because something has changed. It will delete the entries, and find the next blank row, but the .paste function will not operate. (Even though it is on the cell I want to paste into and I can see the data in the clipboard.)

I have gotten Run Time Error 1004 "Paste method of worksheet class failed," and "PasteSpecial Method of Range class failed," as well as 438 "Object doesn't support this property or method."

I'm relatively new to Excel VBA. I have tried different variations of .paste and .pastespecial and nothing seems to work. I have tried With statements, and defining the range. I'm at a loss.

Any ideas or suggestions, would be greatly appreciated.

Sub ImportAPDR()

Dim wbImport As Workbook
Dim wbCurrent As Workbook
Dim strSHC As String

Set wbImport = Workbooks("ImportPhase2.xlsm") 'Ensure name of the workbook... and don't change it.
Set wbCurrent = ActiveWorkbook

'On Error GoTo Handler:
Application.ScreenUpdating = False 'Prevents flickering screen.

'Activate Page 3 of the APDR

Worksheets("Page 3").Activate

strSHC = Range("A11").Value

Range("A11").Activate

Do

    If ActiveCell.Value = "" Then Exit Do
    ActiveCell.Offset(1, 0).Activate

Loop

'Selects All data from A11 until the EOR
ActiveCell.Offset(-1, 12).Activate
Range("A11", ActiveCell).Select

Selection.Copy

'Find the first blank cell in the Import workbook.

wbImport.Activate
Worksheets("Import").Activate

FindSHC (strSHC) 'Must send a variable or the other subroutine will not work.
Application.ScreenUpdating = True

Exit Sub

'Handler:
'MsgBox ("Ensure to run the macro on the APDR workbook, not the import workbook.")

End Sub

Sub FindSHC(strSHC As String)

    Dim foundSHC As String
    Dim Rng As Range
    Dim StartRange As Range
    Dim PasteRng As Range

    FindString = strSHC
    If Trim(strSHC) <> "" Then
        With Sheets("Import").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Rng Is Nothing Then
                Range("A1").Activate
                Do
                    If ActiveCell.Value = "" Then Exit Do
                    ActiveCell.Offset(1, 0).Activate
                Loop
                ActiveSheet.Paste
                Application.CutCopyMode = False
                MsgBox ("The Tenant/Unit Details have been copied for import.")
            Else
                Application.Goto Rng, True
                Set StartRange = ActiveCell
                Answer = MsgBox("This APDR looks like it has already been imported." & vbNewLine & "Do you want to reimport and replace?", vbYesNo)
                If Answer = vbYes Then
                    'Finds the values that have previously been entered and loops to select them all for deletion.
                    Do

                        If ActiveCell.Value <> strSHC Then Exit Do
                        ActiveCell.Offset(1, 0).Activate

                    Loop

                    ActiveCell.Offset(-1, 0).Activate
                    Range(StartRange, ActiveCell).Select
                    Selection.EntireRow.Delete Shift:=xlUp

                    'Find the next open row
                    Range("A1").Activate

                    Do

                        If ActiveCell.Value = "" Then Exit Do
                        ActiveCell.Offset(1, 0).Activate

                    Loop

                    ActiveSheet.Paste    '<-----This is where I get my error.
                    Application.CutCopyMode = False
                    MsgBox ("The Tenant/Unit Details have been replaced.")
                Else
                    Application.CutCopyMode = False
                    MsgBox ("Import has been cancelled.")
                End If
            End If
        End With
    End If

End Sub    

Answer 1

Here are some tips to avoid getting stuck in such moments.

1. Always try to emulate what macro does by manually performing all the actions, wherever it is possible to. In your case you can choose 2 identifiers for QA - one which is not present and another which is present. You won't have any trouble with the first one, but with the second - as soon as you delete any cell content after you have copied some range, your selection and copied range would be lost from clipboard. And that's actually what makes a trouble.

2. Do proper debugging. As soon as you go into strange exception - debug what brought your code to this point. And again - in your case you can see that as soon as you delete contents the "dashed border" around copied range will disappear, meaning you have no copied range anymore which you could paste.

3. This one goes directly to copy-paste combination. Whenever you are in need to use Copy & Paste build up your code so that these two go one after another. Avoid any range interaction between these two functions. If you need to do some calculations just save the range parameters(like range rectangle dimensions, starting row+column, ending row+column) to other variables and Copy only when you've done with everything using those saved parameters.

Good luck with this one.