K
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
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