Object Required - Error 424

test.xlsmHi there,

The goal of the macro is to copy a named range(table) from one sheet to a specific place in another sheet within the same workbook. The destination cell is bounded by some other text. Here is a sample layout (sample file also attached):

Some text

***Copied table***

Some text

The copied table can be of variable size and the "Some text" should wrap that table.

I came up with the code below but there is a problem when the following line is executed

ActiveSheet.Range(cel, cel.Offset(RowDiff - 2, 0)).EntireRow.Select ''Does not work here - Error 424 - Object required

However, if I uncomment the following line in the code it works fine

' ActiveSheet.Range(cel, cel.Offset(RowDiff - 2, 0)).EntireRow.Select ''Works here

Below is the full macro code:

Option Explicit

Dim RowDiff As Integer
Dim ra As String
Dim cel As Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set cel = Worksheets("Sheet2").Range("A8")

If Not Intersect(Target, Range("DB")) Is Nothing Then

RowDiff = 0
Do While cel.Offset(RowDiff, 0).Value <> "End"
RowDiff = RowDiff + 1
If RowDiff > 10000 Then Exit Do
Loop
End If
' MsgBox RowDiff
If RowDiff >= 1 Then
Worksheets("Sheet2").Activate
ActiveSheet.Range(cel, cel.Offset(RowDiff - 2, 0)).EntireRow.Delete Shift:=xlShiftUp
' ActiveSheet.Range(cel, cel.Offset(RowDiff - 2, 0)).EntireRow.Select ''Works here
Range("DB").Copy
Worksheets("Sheet2").Activate
ActiveSheet.Range("A8").Select
Selection.Insert Shift:=xlDown
'Application.CutCopyMode = False
'ra = "A8:A" & RowDiff + 8 - ulgy workaround
'Worksheets(2).Range(ra).EntireRow.AutoFit - ugly workaround
ActiveSheet.Range(cel, cel.Offset(RowDiff - 2, 0)).EntireRow.Select '' Does not work here - Error 424 - Object required
Else: End

' ra = Range("DB").Cells(1, 1).Address(False, False)
' Range("DB").Copy Destination:=Worksheets("Sheet2").Range("ra")

End If

End Sub


I had to use the "ugly workaround", which is commented out in a code, to make the macro work and it does what I want but I still do not understand why I get that "Object required" error.

Any help would be appreciated.

Thank you in advance,
Merdan

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
http://www.excelforum.com/excel-prog...ror-424-a.html
Thread Closed.