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

 

[ipbr21054]

Well thats good as it saves me doing another post about that.

So i only want the code to run IF cell A6 is active.
If did write this below which works but didnt know what to do it A6 wasnt the active cell,I want a Msgbox advising but couldnt sort it

If Not Application.Intersect(ActiveCell, Range("A6")) Is Nothing Then

 

[MARK858]

If A6 isn't the ActiveCell then it wouldn't intersect with A6 with that code

 

[ipbr21054]

If you refer to my one line of code then i maybe dont understand it fully.

My understanding is,
If A6 is selected Then run the code.
BUT
If another cell is selected then do nothing other than show a Msgbox saying Yu Must Select Cell A6

I can skip that if need be a moment BUT stuck just getting it past the original error

 

[MARK858]

My understanding is,
If A6 is selected Then run the code.
BUT
If another cell is selected then do nothing other than show a Msgbox saying Yu Must Select Cell A6

That is what the line does

I can skip that if need be a moment BUT stuck just getting it past the original error

As I stated you need to make Target.Row the actual row you want the code to act on, I can't tell that from the code.
Only you know what row it is or how you are going to define the row.

 

[ipbr21054]

Im confused by understanding the reply.

Im not getting it.
My worksheet that the values are copied on are row 6
So A6 is the customers name.

So if A6 is selected / Active then run the code & paste value to my otherworksheet.

 

[MARK858]

That line that is erring has nothing to do with restricting the running of the the code, it just defines the range to be copied, if you want it to copy the cell on row 6 or the activecell row then change Target.Row to .Rows("6:6") or ActiveCell.Row.

If you only want it to run if A6 is the activecell then you need to add a separate test for it. Sort the error you asked about first so it is running, then worry about putting in a test to restrict it's use.

 

[ipbr21054]

Thats why i posted this as i cant sort the error

 

[MARK858]

Thats why i posted this as i cant sort the error

I have just told you how to sort the error (and in post 10)...

you want it to copy the cell on row 6 or the activecell row then change Target.Row to .Rows("6:6") or ActiveCell.Row.

Set rng = .Cells(.Rows("6:6").Row, SCol)

or

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

or

Set rng = .Cells(6, SCol)

 

[ipbr21054]

Sometimes i dont understand what ive been told due to learning issues.

I have this in place & now transfering values to the worksheet.
BUT
Its transfered value from row A9 when i had a different row selected ?

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("KDX2LIST")
    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(ActiveCell.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

 

[ipbr21054]

Something isnt right.
I run the code 3 times & each time it transfered different customers & different values,admitingly from the wrong row but....
At least it should of transfered the same customer 3 times but it didnt