target range problem?

mike79

New Member
Joined
Oct 31, 2005
Messages
14
This is my first time here. :)
Lots of tips!
I have one that I couldn't not find on the boards.
What I want to do is for cells c5-c31 to be added to another wooksheet when something is inserted into the cells.
I was just able to get cell c5 to copy over. I tried setting the range to C:C, but a debug dialog poped up, also tried C5:C31, that didn't work also.
That's my first problem.
My second, everytime I change that value on C5, it inserts the changed text into the next cell below. Is there a way I can just have the changed data stay on the cell it copied to? I would like this for the remaining cells from c6:c31.
Below is the part for the first problem, I haven't figured out the script for the second problem.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("c5") Then Exit Sub

Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Target
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Welcome to MrExcel Board!

Try this in your particular worksheet code module:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 1 Then Exit Sub                                          'more than 1 cell selected
    If Application.Intersect(Target, Range("C5:C31")) Is Nothing Then Exit Sub  'not in right range

    Application.EnableEvents = False

    Target.Copy Worksheets(2).Cells(Target.Row, Target.Column)

    Application.EnableEvents = True
End Sub

That will copy anything that gets changed in C5:C31, as long as it's one cell at a time, to the second worksheet in the workbook.

Hope that helps!
 
Upvote 0

Forum statistics

Threads
1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

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