Copy paste from another sheet

[Nick70]

Hi,

I have a macro that runs when dropdown in C8 has value "Client" (in sheet Relationship Profile Summary Form).

When C8 is "Client" then two macros are called macro Unhide and macro Paste.

I get an error message with macro Paste.
Error says: Run-time error '1004':
Method 'Paste' of object'_Worksheet' failed

Can you please see code for macro Paste and let me know if you see anything wrong?

Sub Paste()

Sheets("Sheet1").Activate

    Range("C6:C6").Select
    Selection.Copy
    
Sheets("Relationship Profile Summary").Activate
    
    Range("D40:D40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 End Sub

The macro Paste is supposed to copy cell C6 from Sheet1 and paste it in sheet Relationship Profile Summary in cell D40.

Also the macro that kicks off macro Paste has code below:

Sub worksheet_change(ByVal target As Range)

If target.Address(0, 0) = "B4" Then
If target.Value = "Escalation Form" Then
 Call Expand
ElseIf target.Value = "Relationship Profile Summary Form" Then
Call Collapse
End If
ElseIf target.Address(0, 0) = "C8" Then
Set target = Range("C8")
If target.Value = "Counterparty" Then
Call Hide
Call Clear
ElseIf target.Value = "Client" Then
Call Unhide
Call Paste
ElseIf target.Value = "Business Partner" Then
Call Unhide
Call Paste
End If
End If

End Sub

Many Thanks,
N.

 

[MARK858]

What happens with

Sub Paste()

Sheets("Sheet1").Range("C6").Copy
    
Sheets("Relationship Profile Summary").Range("D40").PasteSpecial Paste:=xlPasteValues
 
End Sub