Range of Object Failure

Question

When referencing a named range, I'm getting a Global 'Range of Object Failure

I've tried to use a named range determined by user input as ranges to copy and paste...

Essentially copy rows( "SKE") range(1:"Daily) from source sheet

and paste it into shStations(i)

do some formatting then next i

then

copy the same rows from shSource again and paste them onto shTarget

But it can't seem to get past the ranges...

Sub SendBlue25()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim shHelper As Worksheet
Dim Rnga As Range
Dim Rngb As Range
Dim Rngc As Range
Dim Rngd As Range


'set wb's
Set wbSource = Workbooks.Open(Filename:="C:\Schedule.XLSX", ReadOnly:=True)
Set wbTarget = ThisWorkbook

'Set ws's
Set shSource = wbSource.Worksheets("Schedule")
Set shTarget = wbTarget.Worksheets("Master")
Set shHelper = wbTarget.Worksheets("Start")

'set ranges for math
Set Rnga = shHelper.Range("A1")
Set Rngb = shHelper.Range("B1")
Set Rngc = shHelper.Range("C1")

'columns to hide
Set Rngd = shTarget.Range("A:B, F:F, I:I, K:L, N:AD")


'name the ranges to be able to call them from inside excel
wbTarget.Names.Add Name:="SKE", RefersTo:=Rnga  '---wherever i try to reference ---
wbTarget.Names.Add Name:="Daily", RefersTo:=Rngb  '---one of these ranges ----
wbTarget.Names.Add Name:="Another", RefersTo:=Rngc   '-----I get a ----


Application.ScreenUpdating = False

'clear the master and allow excel to make calculations
shTarget.Rows(Range(4)).Rows(Range("1:" & Range("Daily"))).Clear   '---Run-time error '1004' Method "Range" of object"_Global'failed----
shHelper.Calculate

'set sheets array
shStations = Array("Denest ", "Decontent ", "Columns ", "Unishell ", "5060 ", "EOL ")

'copy source paste to shStation with offset -next sheet repeat
For i = 0 To UBound(shStations)
   
  shSource.Rows(shHelper.Range("SKE") + 2 - i).Rows("1:" & shHelper.Range("Daily")).Copy
       With shStations(i)
      .Rows(.Range("Daily") * i + ("3" + i)).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
      .Rows(Range(2)).Rows("1:" & shTarget.Range("Daily") * 8).Font
        .Name = "Calibri"
        .Size = 22
End With
Next i
Application.CutCopyMode = False

'copy source paste to shTarget -next range repeat
For i = 0 To UBound(shStations)
   
      shSource.Rows(shTarget.Range("SKE") + 2 - i).Rows("1:" & shTarget.Range("Daily")).Copy
      shTarget.Rows(Range("Daily") * i + ("3" + i)).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Next i

'Add title and date for each
For i = 0 To UBound(shStations)
     
     shTarget.Cells(Range("Daily") * i + ("2" + i), 4).Value = shStations(i) & Date
    Next i
    With shTarget
    shTarget.Rows(Range(3)).Rows("1:" & Range("Daily") * 8).Font
    .Name = "Calibri"
    '.Size = 22
        
Application.CutCopyMode = False

 

With shTarget.Rows(2).Rows("1:" & Range("Daily") * 8).Columns.AutoFit
    shTarget.Columns(4).Columns.ColumnWidth = 25
    shTarget.Rows(2).Rows("1:" & Range("Daily") * 8).Rows.RowHeight = 35
    End With
    
    Rngd.EntireColumn.Hidden = True
    
    
         Application.ScreenUpdating = True
End With

End Sub

Answer 1

• I am not sure why there are many Rows().Rows() in your code.

eg.

'clear the master and allow excel to make calculations
shTarget.Rows(Range(4)).Rows(Range("1:" & Range("Daily"))).Clear   '---Run-time error '1004' Method "Range" of 

• I guess you try to clear a range which starts A4 on shTarget. Assumes shHelper.Range("Daily") is cells count in the range.

Please try

shTarget.Cells(4,1).Resize(1, shHelper.Range("Daily")).Clear