copy cell after resize method in VBA
Dear all,
I am new to VBA, and going to make a label form for junior, thanks to Norman
Jones , Bob Phillips and Mangesh_yadav for their advise . They made me have
a good begining.
I am going to copy cell A1 and paste into A2 and A3 once the user choose
something from the drop down list at Col A by using Private Sub
Worksheet_Change method.
Now everything seems fine and the formatting in A1 is perfect, but I want
the format in A1 would be paste into my target cell (A2 and A3),not only the
value, and the follow code seems can not do this.
Is there any body can help?
Thanks
My coding :
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:b12")) Is Nothing Then
If Not IsEmpty(Target) Then
With Target.Font
.Name = "Arial"
.FontStyle = "standard"
.Size = 18
End With
With Target.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "standard"
.Size = 28
End With
Application.CutCopyMode = False
With Target
.Copy
.Resize(1, 2).Offset(0, 1) = .Value
End With
Application.CutCopyMode = ture
End If
End If
xit:
Application.EnableEvents = True
End Sub
|