VBA 'Object required' error but works in new worksheet

Question

I have a column containing a spill range:

Cat
Dog
Cow
Pig
Chicken

The aim is to put these text values in another column but with 5 empty rows in between each value.

When new values are added into the spill range, the other column is updated continuing the 'every 5 rows' pattern. Likewise when values are removed.

The VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range(Range("G2").SpillParent.Address)) Is Nothing Then
    
    Columns(55).ClearContents
    
    Dim srange As Range

    Set srange = Range("G2").SpillingToRange
    Range("BD2") = Range("G2").SpillingToRange.HasSpill
    Range("BD4") = Range("G2").SpillingToRange.Address
    Range("BD6") = Range("G2").SpillingToRange.count

    Dim count As Long, i As Long, nextrow As Long

    count = Range("G2").SpillingToRange.count + 1
    nextrow = 2

    For i = 2 To count
        Sheet1.Cells(nextrow, 55).Value = Sheet1.Cells(i, 7).Value
        nextrow = Cells(nextrow, 55).Offset(6, 1).Row
    Next
    
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

The code returns an Object required error when I enter the spill formula again to update the worksheet. When I went to track down the problem in a new worksheet, the code works fine and the Object required error does not appear. The spill range calculates its values based on another column.

Any help will be greatly appreciated.

What [error trapping mode](https://stackoverflow.com/q/12687105/11683) do you have? — GSerg, Jun 26 '21 at 13:11
It seems unusual for you to be referring to a specific worksheet (`codename: =sheet1`) when you are running worksheet code. I haven't analyzed what you are doing, but is that really your intent? If not, could it be causing a problem? — Ron Rosenfeld, Jun 26 '21 at 19:56
@RonRosenfeld I changed the `sheet1` but still received the same error. — Henry, Jun 27 '21 at 03:59
@RonRosenfeld The worksheet was not actually called `sheet1` so I changed the name to the actual name of the worksheet. — Henry, Jun 27 '21 at 11:52
Why are you using the worksheet codename at all? And do you mean to use the `codename` or the `name`? — Ron Rosenfeld, Jun 27 '21 at 12:27
@RonRosenfeld I tried without worksheet name as well, but with no success. I am relatively sure I used the `name` as in the name I entered when renaming the worksheet, not the `codename`. — Henry, Jun 28 '21 at 10:16
If you are using `Sheet1.Cells(...`, `Sheet1` will be the `codename`, not the `name` — Ron Rosenfeld, Jun 28 '21 at 12:11

VBA 'Object required' error but works in new worksheet

0 Answers0