VBA copy 'corrupts' font selection (XL2003)

[ker_01]

I have raw data on Sheet3, with the entire sheet formatted in Times New Roman.

I have a form on Sheet1 where I copy sections of data from Sheet3 in a
desired order. I also took Sheet1, selected the whole sheet, and formatted in
TNR

Then I run the code below; all data that is copied over from Sheet3 keeps
getting pasted in Arial format!

Any ideas on what would cause this? I'd prefer to fix the root cause, rather
than "band-aid" it by just forcing a reapplication of the desired font after
all records are pasted.

Thank you,
Keith

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.Unprotect "ABC"
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
If Target = Sheet1.Range("F1") Then

SelectedOwner = Target.Value

LastSourceRow = lastRow(Sheet3)
PasteRow = 6

Sheet1.Rows("7:100").Select
Selection.EntireRow.Hidden = False

Sheet1.Range("A7:K100").Clear

For I = 2 To LastSourceRow
If Sheet3.Range("AF" & I).Value = SelectedOwner Then
PasteRow = PasteRow + 1
Sheet1.Range("A" & PasteRow).Value = Sheet3.Range("A" &
I).Value
Sheet1.Range("B" & PasteRow).Value = Sheet3.Range("X" &
I).Value
Sheet1.Range("C" & PasteRow).Value = Sheet3.Range("Y" &
I).Value
Sheet1.Range("D" & PasteRow).Value = Sheet3.Range("Z" &
I).Value
Sheet1.Range("E" & PasteRow).Value = Sheet3.Range("AA" &
I).Value
Sheet1.Range("F" & PasteRow).Value = Sheet3.Range("U" &
I).Value
Sheet1.Range("G" & PasteRow).Value = Sheet3.Range("B" &
I).Value
Sheet1.Range("H" & PasteRow).Value = Sheet3.Range("C" &
I).Value
Sheet1.Range("I" & PasteRow).Value = Sheet3.Range("E" &
I).Value
Sheet1.Range("J" & PasteRow).Value = Sheet3.Range("F" &
I).Value
Sheet1.Range("K" & PasteRow).Value = Sheet3.Range("G" &
I).Value
End If
Next

End If
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Sheet1.Rows((PasteRow + 1) & ":100").Select
Selection.EntireRow.Hidden = True
Sheet1.Range("L7").Select

Sheet1.Protect "ABC"


End Sub

 

[Ryan H]

I had this issue also once. When you use the Value property just the value
come over from Sheet3 to Sheet1. Thus if you have Arial font in Sheet1 the
cell contents will take on Arial font, kinda like SpecialPaste. Use the Text
property instead. Hope this helps! If so, let me know, click "YES" below.

Sheet1.Range("A" & PasteRow).Value = Sheet3.Range("A" & I).Text

 

[Barb Reinhardt]

Would .value2 work any better?