Simple Copy Paste based on another column

H

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
Fluff said:
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
Click to expand...


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
Fluff said:
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.
Click to expand...

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


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
Fluff said:
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
Click to expand...


Tried the code, still the same Stack overflow error
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Hide or Unhide a Tab in a Workbook, if one value of a Column is true
Replies
3
Views
152
DanteAmor
DanteAmor
gheyman
  • Solved
Copy from one workbook to another (just format and values)
Replies
2
Views
417
gheyman
gheyman
M
  • Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
166
mecerrato
M
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
339
USNA91
U
I
  • Solved
Copy and Paste from One workbook to another
Replies
3
Views
435
ipon70
I

Forum statistics

Threads
1,214,792
Messages
6,121,612
Members
449,039
Latest member
Mbone Mathonsi

Share this page

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