Copy cells
 

Hi Erik

Try replacing your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim copyRng As Range

Set copyRng = Range("A1:B20") '<<<<<<<<<CHANGE

If Not Intersect(Target, copyRng) Is Nothing Then
With Worksheets("Sheet2").Range(Target.Address)
.Value = Target.Value
End With
End If

End Sub


Note that you will need to change the line:

Set copyRng = Range("A1:B20")

to reflect the range that you want to copy - I've just used a wild guess
nonsense range.

---
Regards,
Norman



"Erik" wrote in message
...
I have to following sub in my worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet2").Range(Target.Address)
.Value = Target.Value
End With
End Sub
It copies all cell values from sheet1 to sheet2. How do I change this to
copy only a selected range of cells.

Copy cells
 

Hi Erik,

If you want the receiving range on Sheet2 to be (say) C11:D30, you could
replace your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim copyRng As Range

Set copyRng = Range("A1:B20") '<<<<<< CHANGE

If Not Intersect(Target, copyRng) Is Nothing Then
With Worksheets("Sheet2"). _
Range(Target.Address).Offset(10, 2) '<<<<< CHANGE

.Value = Target.Value
End With
End If

End Sub

You can alter the receiving range on Sheet2 by altering the Offset
co-ordinates above.

Set the copy range and receiving ranges to suit your personal requirements.

---
Regards,
Norman


"Erik" wrote in message
...
First off, I would like to appologize for the multiple postings of the
same question. Sun and Mon nothing I posted was showing up. Not sure if I
had missed something, I tried several times.
The code you supplied works great. I have one more question though. How
can I get it to copy range from sheet1 to a different range in sheet2?
Erik

"Norman Jones" wrote:

Hi Erik

Try replacing your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim copyRng As Range

Set copyRng = Range("A1:B20") '<<<<<<<<<CHANGE

If Not Intersect(Target, copyRng) Is Nothing Then
With Worksheets("Sheet2").Range(Target.Address)
.Value = Target.Value
End With
End If

End Sub


Note that you will need to change the line:

Set copyRng = Range("A1:B20")

to reflect the range that you want to copy - I've just used a wild guess
nonsense range.

---
Regards,
Norman



"Erik" wrote in message
...
I have to following sub in my worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet2").Range(Target.Address)
.Value = Target.Value
End With
End Sub
It copies all cell values from sheet1 to sheet2. How do I change this
to
copy only a selected range of cells.

Copy cells
 

Great. That did the job. Thank you.

"Norman Jones" wrote:

Hi Erik,

If you want the receiving range on Sheet2 to be (say) C11:D30, you could
replace your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim copyRng As Range

Set copyRng = Range("A1:B20") '<<<<<< CHANGE

If Not Intersect(Target, copyRng) Is Nothing Then
With Worksheets("Sheet2"). _
Range(Target.Address).Offset(10, 2) '<<<<< CHANGE

.Value = Target.Value
End With
End If

End Sub

You can alter the receiving range on Sheet2 by altering the Offset
co-ordinates above.

Set the copy range and receiving ranges to suit your personal requirements.

---
Regards,
Norman