Run Time Error 424 Object required

[ipbr21054]

Hi,
I was advised a very good code by @RoryA & decided to use it again.
I have changed worksheet names & copy / paste columns etc but im getting a RTE of 424 OBJECT REQUIRED when i run it.
Can you point me in the direction of finding the cause please

This is the code in use shown below

Private Sub Kdx2_Click()
    
    Dim WB As Workbook, DestWB As Workbook
    Dim ws As Worksheet, DestWS As Worksheet
    Dim rng As Range, rngDest As Range
    Dim ColArr As Variant, SCol As Variant, DCol As Variant
 
    On Error Resume Next
    Set DestWB = Application.Workbooks("CLONING-KDX2.xlsm")

    If DestWB Is Nothing Then
        Workbooks.Open fileName:="C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\CLONING-KDX2.xlsm"
        Set DestWB = Application.Workbooks("CLONING-KDX2.xlsm")
    End If
    On Error GoTo 0

    Set WB = ThisWorkbook
    On Error Resume Next
    Set ws = WB.Worksheets("DATABASE")
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "Worksheet 'DATABASE' IS MISSING"
        Exit Sub
    End If
 
    Set DestWS = DestWB.Worksheets("CLONING")
    ColArr = Array("A:A", "D:B", "G:C", "N:D", "M:E", "L:F", "I:G")
    Dim DestNextRow As Long
    With DestWS
        If IsEmpty(.Range("A" & 1)) Then
            DestNextRow = 1
        Else
            DestNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        End If
    End With

    Application.ScreenUpdating = False
    For Each SCol In ColArr
        DCol = Split(SCol, ":")(1)
        SCol = Split(SCol, ":")(0)
        With ws
            Set rng = .Cells(Target.Row, SCol)
        End With

        With DestWS
            Set rngDest = .Range(DCol & DestNextRow)
        End With
        rng.Copy
        rngDest.PasteSpecial PASTE:=xlPasteValues
        
        rngDest.Borders.Weight = xlThin
        rngDest.Font.Size = 16
        rngDest.Font.Bold = True
        rngDest.HorizontalAlignment = xlCenter
        rngDest.Cells.Interior.ColorIndex = 6
        rngDest.Cells.RowHeight = 25
    Next SCol
    Application.ScreenUpdating = True
   
 
With ActiveWorkbook ' THIS WILL SAVE & CLOSE CLONING-KDX2 WORKBOOK
   .Save
   .Saved = True
   .Close
End With

End Sub

 

[MARK858]

Usual question... what row is highlighted when you click Debug? If you haven't got a debug option what line does it stop at when you step through the code with F8?

 

[ipbr21054]

Hi,
On my phone at present so hope this works.
Original post towards bottom.

Set rng = .Cells(Target.Row, SCol)

 

[MARK858]

Where are you defining Target?

 

[ipbr21054]

I copied this code from a working code.
Only thing I changed was workbook, worksheet name & what to copy etc.

Didn’t touch anything else.
The worksheet it was copied from works.

 

[MARK858]

Did you change the Sub name?

 

[ipbr21054]

Yes.

Other was a double click.

This code is now on a command button.
Does that make any difference ?

 

[MARK858]

Does that make any difference ?

Yes, the Double click uses a Target cell, Target in a regular sub is just a normal variable (although it would be a bad idea naming it as such)

 

[ipbr21054]

How would I go about sorting this please.

 

[MARK858]

Depends what row you are trying to get the code to work on, possibly ActiveCell.Row rather than Target.Row but only you know that.