Simple Copy Paste based on another column

[H_gupta]

Hi,

I am new to coding, and a basic code is not working for me. Looking for help
Following is my complete code which does other functions and I have added a small code in the end to copy paste a value from cell to other cells based on a condition. Basically, I want to default a value of a cell in multiple cells based on a condition.
But only that part is not working, rest of the code works as expected.

If [C10] = "Yes" Then
Range("F10").Select
Range("F10").Copy
Range("C42","I42").Select
Range("C42","I42").Paste

Private Sub Worksheet_Change(ByVal Target As Range)


Sheets("BG1").Tab.ColorIndex = 10
Sheets("BG2").Tab.ColorIndex = 10
Sheets("BG3").Tab.ColorIndex = 10
Sheets("BG4").Tab.ColorIndex = 10
Sheets("BG5").Tab.ColorIndex = 10
Sheets("BG6").Tab.ColorIndex = 10


If [C4] = "1" Then
Sheets("BG2").Visible = False
Sheets("BG3").Visible = False
Sheets("BG4").Visible = False
Sheets("BG5").Visible = False
Sheets("BG6").Visible = False


Else
If [C4] = "2" Then
Sheets("BG1").Visible = True
Sheets("BG2").Visible = True
Sheets("BG3").Visible = False
Sheets("BG4").Visible = False
Sheets("BG5").Visible = False
Sheets("BG6").Visible = False




Else
If [C4] = "3" Then
Sheets("BG1").Visible = True
Sheets("BG2").Visible = True
Sheets("BG3").Visible = True
Sheets("BG4").Visible = False
Sheets("BG5").Visible = False
Sheets("BG6").Visible = False




Else
If [C4] = "4" Then
Sheets("BG1").Visible = True
Sheets("BG2").Visible = True
Sheets("BG3").Visible = True
Sheets("BG4").Visible = True
Sheets("BG5").Visible = False
Sheets("BG6").Visible = False


Else
If [C4] = "5" Then
Sheets("BG1").Visible = True
Sheets("BG2").Visible = True
Sheets("BG3").Visible = True
Sheets("BG4").Visible = True
Sheets("BG5").Visible = True
Sheets("BG6").Visible = False


Else
If [C4] = "6" Then
Sheets("BG1").Visible = True
Sheets("BG2").Visible = True
Sheets("BG3").Visible = True
Sheets("BG4").Visible = True
Sheets("BG5").Visible = True
Sheets("BG6").Visible = True




[COLOR=#ff0000][B]If [C10] = "Yes" Then[/B][/COLOR]
[COLOR=#ff0000][B]Range("F10").Select[/B][/COLOR]
[COLOR=#ff0000][B]Range("F10").Copy[/B][/COLOR]
[COLOR=#ff0000][B]Range("C42","I42").Select[/B][/COLOR]
[COLOR=#ff0000][B]Range("C42","I42").Paste[/B][/COLOR]




End If


End If
End If
End If
End If
End If
End If


End Sub

 

[Fluff]

Do you have any other event code on that sheet, or in the ThisWorkbook module?

 

[H_gupta]

Do you have any other event code on that sheet, or in the ThisWorkbook module?

There is another sheet in the workbook which has the following code


Private Sub Worksheet_Change(ByVal Target As Range)


If [B6] = "Create a new WBS Code" Then
Sheets("BG1").Visible = True
Sheets("Modify WBS").Visible = False


Else


If [B6] = "Modify an existing WBS Code" Then
Sheets("Modify WBS").Visible = True
Sheets("BG1").Visible = False
Sheets("BG2").Visible = False
Sheets("BG3").Visible = False
Sheets("BG4").Visible = False
Sheets("BG5").Visible = False
Sheets("BG6").Visible = False


End If
End If


End Sub



I tried removing the copy paste code, and then both these codes are working fine.
Only when I add the Cope paste code, i get the stack overflow error

 

[H_gupta]

Any other workaround will also do.

there are multiple cells where I want to default a single value of another cell
I used If (condition) formula as well, but then i cannot write anything in that cell as the formula deletes.

So I want to be able to write in the cell, and have the cell default to a fixed value as well based on the condition.

 

[Fluff]

That other code won't affect anything if it's in a different sheet.

Can't think why you would get a stack overflow error.
Are any of the cells merged?
Do you want to copy F10 to C42 & I42, or to copy it to C42:I42?
What is in F10?

 

[H_gupta]

That other code won't affect anything if it's in a different sheet.

Can't think why you would get a stack overflow error.
Are any of the cells merged?
Do you want to copy F10 to C42 & I42, or to copy it to C42:I42?
What is in F10?

I want to copy F10 to C42 & I42 separately, not the entire range
these cells are not merged, but these cells have some conditional formatting in the background like that of Colour, font, text etc.
Will that interfere with macro ?

F10 has a number like 123456


I used the same code (just the copy paste code ) in a new blank sheet for testing with no other data or conditions. and its giving the same stack overflow error.

 

[Fluff]

I not sure this will help, but try

If Range("C10") = "Yes" Then
   Range("C42,I42").Value = Range("F10").Value
End If

 

[H_gupta]

I not sure this will help, but try

If Range("C10") = "Yes" Then
   Range("C42,I42").Value = Range("F10").Value
End If

I tried this code :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("C10").Address Then
If Target = "Yes" Then
Application.EnableEvents = False
Range("C42,G42,I42").Value = Range("F10").Value
Application.EnableEvents = True
End If
End If

End Sub

And it is working fine, no stackoverflow error. But if I dynamically the value of F10, it does not automatically change the cell values of C42, G42 and I42.
I have reselect C10 as Yes, and then it comes.
Although C10 is already "Yes" , I have to select it again.

Can this be done dynamically ?

 

[Fluff]

How is F10 being changed?

 

[H_gupta]

How is F10 being changed?

Manually by entering a number in the cell

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("F10").Address Then
   If Range("C10").Value = "Yes" Then
      Application.EnableEvents = False
      Range("C42,G42,I42").Value = Range("F10").Value
      Application.EnableEvents = True
   End If
End If

End Sub