| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA copy 'corrupts' font selection (XL2003)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA copy 'corrupts' font selection (XL2003)
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 -- Cheers, Ryan "ker_01" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA copy 'corrupts' font selection (XL2003)
Would .value2 work any better?
-- HTH, Barb Reinhardt "Ryan H" wrote: 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 -- Cheers, Ryan "ker_01" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Copy, Paste not working properly, corrupts file | Excel Discussion (Misc queries) | |||
| Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
| Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
| Worksheet Copy fails (xl2003) | Excel Programming | |||
| Weird range selection issue XL2003- help please! | Excel Programming | |||
Linear Mode
