PDA

View Full Version : Solved: Problem with copy/paste "Formats"



ndendrinos
02-02-2011, 07:09 PM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F17:F35")) Is Nothing Then

ActiveCell.Offset(-1, -3).Select 'Selects cell in same Row, 3 Columns LEFT
Selection.Copy
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlPasteFormats
End If
End Sub

two questions & one problem with above
1) ActiveCell.Offset(-1, -3).Select keeps me on the same row (0,3)does not... any idea why?

2) Why after running the code the cursor always chooses [F16]... any idea why?

3) If the value change in [F17:F35] is due to direct input then the code works
If the value changes because of a formula it does not.
Can this be addressed so
that the code will work if the change is caused by a formula?

Many thanks

mancubus
02-03-2011, 12:04 AM
hi.

try


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F17:F35")) Is Nothing Then
Target.Offset(, -3).Copy
Target.Offset(, 3).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End If
End Sub

mancubus
02-03-2011, 12:17 AM
and for Q3:

http://www.mvps.org/dmcritchie/excel/event.htm

ndendrinos
02-03-2011, 07:19 AM
hello mancubus & thank you for your suggestion.
the code works but now I realize that my concept was wrong to begin with.
Q1 & Q2 are no longer important
I have changed my code and now it works BUT it is not perfect.
The problem is that from Sheet "Invoice" from the selection on the userform it finds a match in sheet "Product" but when it copies the data it does not copy the format of the data as well.
My attachment will explain it better.
Thank you

ndendrinos
02-03-2011, 08:42 AM
Small change and now it works
Thank you mancubus