Excel VBA runtime-error 1004 in error handling

Question

This is my first time posting, so forgive me if I make any mistakes.

I have the following code running in a workbook to open 3 other workbooks, copy from them and paste in the original workbook.
I have set up error handling to catch which sheet throws an error, if any to provide help when troubleshooting.
For the 1st 2 sheets everything works fine. Usually the error is thrown when the file cannot be found due to a misnaming.

However, the third file "wellsFile" triggers the error handling and gives me a

1004 range of object _Global failed

specifically at the With Range("O34") line.

As far as I can tell, the i=3 error handling section is the same as the other 2.

I have tried fully qualifying the range selection with activesheet and activeworkbook and the error changes to application defined. I have tried other ranges and that also throws the error.
I know that the wbk.activate and sheets activation are working as they do take me where I want.

Any help is appreciated here.

Sub rateImport()

'created by Brad Chandler
'7-21-17
'goal is to take the rate sheets from desktop, copy and paste certain sections to certain sections
'of the Pricing Input sheet

Dim conName As String
Dim govName As String
Dim wellName As String
Dim conFile As String
Dim govFile As String
Dim wellsFile As String
Dim govWbk As Workbook
Dim conWbk As Workbook
Dim wellsWbk As Workbook
Dim Wbk As Workbook
Dim i As Integer    'for error assistance


On Error GoTo errorhandler

Application.ScreenUpdating = False

'setting up Con and Gov file names for opening
conName = Trim(Sheets("Dashboard").Range("O32").Value)  'removes beginning and ending spaces
conFile = "C:\Users\" & (Environ$("Username")) & "\Desktop\" & conName

govName = Trim(Sheets("Dashboard").Range("O33").Value)
govFile = "C:\Users\" & (Environ$("Username")) & "\Desktop\" & govName

wellName = Trim(Sheets("Dashboard").Range("O34").Value)
wellsFile = "C:\Users\" & (Environ$("Username")) & "\Desktop\" & wellName

'opening and copying rates over
Set Wbk = ActiveWorkbook
Wbk.Sheets("Pricing Input").Activate

    'copying from Conventional
    i = 1
    Workbooks.Open Filename:=conFile
    Set conWbk = ActiveWorkbook

    conWbk.Sheets("CSV File").Activate
        '30 year Copy
        Range("B2:F14").Copy
    Wbk.Activate
        Range("A3").PasteSpecial xlPasteValues

    conWbk.Activate
        '20 year copy
        Range("B16:F29").Copy
    Wbk.Activate
        Range("A23").PasteSpecial xlPasteValues

    conWbk.Activate
        '15 year copy
        Range("B31:F43").Copy
    Wbk.Activate
        Range("A43").PasteSpecial xlPasteValues

    'preventing save message from showing
    Application.DisplayAlerts = False
        conWbk.Close
    Application.DisplayAlerts = True

    'copying from Government
    i = 2
    Workbooks.Open Filename:=govFile
    Set govWbk = ActiveWorkbook

    govWbk.Sheets("CSV File").Activate
        '30 year copy
        Range("B5:F21").Copy
    Wbk.Activate
        Range("L3").PasteSpecial xlPasteValues

    govWbk.Activate
        '15 year copy
        Range("B31:F47").Copy
    Wbk.Activate
        Range("L23").PasteSpecial xlPasteValues

    'prevening save message from showing
    Application.DisplayAlerts = False
        govWbk.Close
    Application.DisplayAlerts = True

'opening and copying Wells Rates
Wbk.Sheets("Wells Pricing").Activate
    i = 3
    Workbooks.Open Filename:=wellsFile
    Set wellsWbk = ActiveWorkbook

    wellsWbk.Sheets("Conf Pricing").Activate
        '30,20,15 Con copy
        Range("B21:J49").Copy
    Wbk.Activate
        Range("A1").PasteSpecial xlPasteValues
    'lock term pricing
    wellsWbk.Activate
        Range("B51:H62").Copy
    Wbk.Activate
        Range("A31").PasteSpecial xlPasteValues


    wellsWbk.Sheets("Govt").Activate
        'FHA VA pricing copy
        Range("B14:K39").Copy
    Wbk.Activate
        Range("N1").PasteSpecial xlPasteValues

    wellsWbk.Activate
        'USDA pricing copy
        Range("B87:F105").Copy
    Wbk.Activate
        Range("N29").PasteSpecial xlPasteValues

    'preventing save message from showing
    Application.DisplayAlerts = False
        wellsWbk.Close
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

Exit Sub


    errorhandler:
    If i = 1 Then
        Wbk.Activate
        Sheets("Dashboard").Activate
        With Range("O32")
            .Select
            .Font.Bold = True
            .Interior.Color = RGB(253, 123, 151)
        End With

        Exit Sub
    Else
        If i = 2 Then
            Wbk.Activate
            Sheets("Dashboard").Activate
            With Range("O33")
                .Select
                .Font.Bold = True
                .Interior.Color = RGB(253, 123, 151)
            End With

            Exit Sub
        Else
            If i = 3 Then
                Wbk.Activate
                Sheets("Dashboard").Activate
                **With Range("034")**
                    .Select
                    .Font.Bold = True
                    .Interior.Color = RGB(253, 123, 151)
                End With
                Exit Sub
            End If
        End If
    End If

    End Sub

*I have removed some of the code that is irrelevant

Absinthe Absinthe · Accepted Answer · 2017-11-09T15:51:44

A few issues:

With Range("034") should not have the asterisks around it (don't know if you put them there for the purpose of this question?)
The O is actually a zero - change it to O
It's always a good idea to prefix a range with the sheet and (since the code involves multiple workbooks) the workbook e.g.

With wbk.Sheets("Dashboard").Range("O34")

Excel VBA runtime-error 1004 in error handling

2 Answers