VBA getting runtime error when trying sum function on named range

Question

Hello i'm trying to put 2 sheets from 2 different workbooks in to one workbook so in the end there is one workbook with 3 sheets(GrNegLos,VýsRek,NegLos) in it. When i'm trying to use sum function in sheet(VýsRek) using this line of code:

With VýsRek
NetLossSAP = Application.WorksheetFunction.Sum(Range("DATA17"))
End With

It gives me Runtime error 1004 Method range of object Global failed. When i check the workbook i can see that worksheet(NegLos) is active when i click on sheet(VýsRek) then it works. But i have named range and i specify in which worksheet i want to use sum function. I don't understand why it is happening.

    Sub Dáta()
Dim dátum As String
Dim TotalNLos, Loss10 As Integer
Dim GrossLoss, NetLossSAP As Double

Dim SAP As Workbook
Dim LORD_rep As Workbook
Dim dash As Workbook

Dim GrNegLos As Worksheet
Dim VýsRek As Worksheet
Dim NegLos As Worksheet

Dim rw As Long
Dim col As Long

Application.ScreenUpdating = False

Set dash = Workbooks.Open("")
Set GrNegLos = dash.Worksheets("Graf Neg. Loss")


Set SAP = Workbooks.Open("")
Set VýsRek = Worksheets("Výsledok rekonciliácie")
    VýsRek.Move after:=GrNegLos
    SAP.Close savechanges:=False

Set LORD_rep = Workbooks.Open("")
Set NegLos = Worksheets("Negative Losses")
    NegLos.Move after:=GrNegLos
    LORD_rep.Close savechanges:=False


Set NegLos = dash.Worksheets("Negative Losses")
Set VýsRek = dash.Worksheets("Výsledok rekonciliácie")

With VýsRek
NetLossSAP = Application.WorksheetFunction.Sum(Range("DATA17"))
End With
GrossLoss = NegLos.Range("CVNŠ")
TotalNLos = NegLos.Range("PNŠ")
Loss10 = NegLos.Range("PNŠvýš10k")
dátum = InputBox("Zadajte dátum")


rw = GrNegLos.Range("A2").End(xlDown).Offset(1, 0).Row
col = GrNegLos.Range("A2").End(xlDown).Offset(1, 0).Column
'Debug.Print rw
'Debug.Print col
    GrNegLos.Cells(rw, col).Value = dátum
    GrNegLos.Cells(rw, col + 1).Value = TotalNLos
    GrNegLos.Cells(rw, col + 2).Value = Loss10
    GrNegLos.Cells(rw, col + 3).Value = GrossLoss
    GrNegLos.Cells(rw, col + 4).Value = NetLossSAP

Application.DisplayAlerts = False
On Error Resume Next

VýsRek.Delete
NegLos.Delete

Application.ScreenUpdating = True

End Sub

Put the worksheet name before the Range("DATA17") Application.WorksheetFunction.Sum(Worksheets(VýsRek).Range("DATA17")) if that is the correct sheet name. — QHarr
@QHarr Thaks for help it works but can you please explain why my way didn't work? — TooMacho BA
@TooMachoBA I suppose QHarr's comment helped. It is the context in which the named range will be searched. Try putting the ActiveSheet.Name to see which sheet is present and you will find that it doesnt have that named range defined. — shahkalpesh

QHarr QHarr · Accepted Answer · 2018-09-25T08:29:44

You need to fully qualify the named range reference so the correct sheet is looked at. If you don't specify then the currently active sheet (ActiveSheet) will be taken as the parent reference implicitly. If that is not the correct sheet, where the named range resides, then you will get the error you observed.

Always fully qualify ranges as not referencing is bug prone e.g.

Application.WorksheetFunction.Sum(VýsRek.Range("DATA17"))

VBA getting runtime error when trying sum function on named range

1 Answers