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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
235
Alex Blakenburg
A
H
  • Question Question
VBA Pivot Table Dynamic Range & Only One Selection per Row
Replies
3
Views
265
DanteAmor
DanteAmor
K
  • Solved
Hide or Unhide a Tab in a Workbook, if one value of a Column is true
Replies
3
Views
303
DanteAmor
DanteAmor
gheyman
  • Solved
Copy from one workbook to another (just format and values)
Replies
2
Views
697
gheyman
gheyman
M
  • Question Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
501
mecerrato
M

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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