Simple Copy Paste based on another column

H_gupta

New Member
Joined
Mar 26, 2019
Messages
33
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




Code:
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
 
Last edited by a moderator:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi & welcome to MrExcel
Try
Code:
Range("C42", "I42").PasteSpecial
However if you are trying to paste into C42 & I42 only try
Code:
Range("C42, I42").PasteSpecial
 
Upvote 0
Hi & welcome to MrExcel
Try
Code:
Range("C42", "I42").PasteSpecial
However if you are trying to paste into C42 & I42 only try
Code:
Range("C42, I42").PasteSpecial


Thanks for replying, tried it. But nothing happens, no error too
 
Upvote 0
Do you want that to sun regardless of the value in C4?
 
Upvote 0
Yes but do you want to look at C10 regardless of the value in C4?
At the moment that part of the code will only run if C4 does not equal 1,2,3,4,5 or 6.
 
Upvote 0
Yes but do you want to look at C10 regardless of the value in C4?
At the moment that part of the code will only run if C4 does not equal 1,2,3,4,5 or 6.

Oh Yes, C4 should not be linked to this part of the code. I am looking at only C10 value.
 
Upvote 0
Oh Yes, C4 should not be linked to this part of the code. I am looking at only C10 value.


I now ended the previous If statements first, and put the last code.
The macro now gives the error :

Run time Error 28: Out of stack space.
and Excel stops responding.

is there a better way to write this?
 
Upvote 0
How about
Code:
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

Select Case Range("C4")
   Case "1"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = 0
      Sheets("BG3").Visible = 0
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "2"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = 0
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "3"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "4"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "5"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = -1
      Sheets("BG6").Visible = 0
   Case "6"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = -1
      Sheets("BG6").Visible = -1
End Select
If Range("C10") = "Yes" Then
   Range("F10").Copy
   Range("C42", "I42").PasteSpecial
End If
End Sub
 
Upvote 0
How about
Code:
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

Select Case Range("C4")
   Case "1"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = 0
      Sheets("BG3").Visible = 0
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "2"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = 0
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "3"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = 0
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "4"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = 0
      Sheets("BG6").Visible = 0
   Case "5"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = -1
      Sheets("BG6").Visible = 0
   Case "6"
      Sheets("BG1").Visible = -1
      Sheets("BG2").Visible = -1
      Sheets("BG3").Visible = -1
      Sheets("BG4").Visible = -1
      Sheets("BG5").Visible = -1
      Sheets("BG6").Visible = -1
End Select
If Range("C10") = "Yes" Then
   Range("F10").Copy
   Range("C42", "I42").PasteSpecial
End If
End Sub


Tried the code, still the same Stack overflow error
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top