Simple Copy Paste based on another column

H_gupta

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

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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
 

H_gupta

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Do you want that to sun regardless of the value in C4?
 

H_gupta

New Member
Joined
Mar 26, 2019
Messages
24

ADVERTISEMENT

Do you want that to sun regardless of the value in C4?

No I want to default the value of F10 in multiple cells (like C42, D42, E42)
if the value of cell C10 is Yes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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.
 

H_gupta

New Member
Joined
Mar 26, 2019
Messages
24

ADVERTISEMENT

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.
 

H_gupta

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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
 

H_gupta

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

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top