Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell Format Same as Target.Address Format
I'm using code where every time a user makes a change anywhere on a specific
sheet, it records that change on another (hidden) sheet. What can I add to my code (copy of which is below) so that the new value that is entered will show up on the hidden sheet formatted the same way (i.e., either as text, 1 decimal, 2 decimals, etc.)? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then With Worksheets("Rates-History") .Activate .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate ActiveCell.Value = Target.Value End With End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell Format Same as Target.Address Format
It would be easier to do a copy.pasteSpecial:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then Target.Copy With Worksheets("Rates-History").(Rows.Count, "A").End(xlUp).Offset(1, 0) .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With Application.CutCopyMode = False End If End Sub -- - K Dales "Paige" wrote: I'm using code where every time a user makes a change anywhere on a specific sheet, it records that change on another (hidden) sheet. What can I add to my code (copy of which is below) so that the new value that is entered will show up on the hidden sheet formatted the same way (i.e., either as text, 1 decimal, 2 decimals, etc.)? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then With Worksheets("Rates-History") .Activate .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate ActiveCell.Value = Target.Value End With End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell Format Same as Target.Address Format
Works great - thanks!
"K Dales" wrote: It would be easier to do a copy.pasteSpecial: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then Target.Copy With Worksheets("Rates-History").(Rows.Count, "A").End(xlUp).Offset(1, 0) .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With Application.CutCopyMode = False End If End Sub -- - K Dales "Paige" wrote: I'm using code where every time a user makes a change anywhere on a specific sheet, it records that change on another (hidden) sheet. What can I add to my code (copy of which is below) so that the new value that is entered will show up on the hidden sheet formatted the same way (i.e., either as text, 1 decimal, 2 decimals, etc.)? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then With Worksheets("Rates-History") .Activate .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate ActiveCell.Value = Target.Value End With End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula will copy the content & format of the target cell? | Excel Discussion (Misc queries) | |||
ActiveCell.Value changes with Format? | Excel Programming | |||
How can i get activecell address | Excel Programming | |||
Activecell address | Excel Programming | |||
ActiveCell.Address | Excel Programming |