the Paste function copies the entire cell (or cells)
including all formatting.
try recording a macro to PasteSpecial Values:
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
You can change it to:
Activesheet.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
hth
Paul
----- Original Message ----
> From: Ken <kthac...@btes.tv>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Tuesday, January 13, 2009 9:38:45 AM
> Subject: $$Excel-Macros$$ Conditional Formatting VBA problem
>
>
> Hi again group,
> I can't find a way to preserve the condtional formatting on a
> worksheet when rows are pasted from another sheet. The condtional
> formatting from the sheet that is being searched transfers to the
> destination sheet and overwrites or completely removes the condtional
> formatting rules from the destination sheet. Here is the macro that I
> have modified and it works very well, except for the formatting
> problem:
> Sub SearchForStringDAILYOUT()
>
> Dim SourceRange As Range, DestRange As Range
> Dim DestSheet As Worksheet, Lr As Long
> Dim LSearchRow As Integer
> Dim LCopyToRow As Integer
> Dim LSearchValue As Date
>
> Application.Run "Delete"
>
> On Error GoTo Err_Execute
>
> 'To keep screen from update flicker
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
>
>
>
> 'Fill in the destination sheet and call the LastRow
> 'function to find the last row
> Sheets("DAILYOUT").Select
> Lr = Sheets("DAILYOUT").Cells(Rows.Count, "A").End(xlUp).Row
>
>
> 'Date value copied from Weekly Job Log G1
> LSearchValue = Range("$G$2")
>
> 'Start search in row 2 in JobLogEntry
> LSearchRow = 2
>
> 'Start copying data to specified row in DAILYOUT (row counter
> variable)
> LCopyToRow = 4
>
> Sheets("JobLogEntry").Select
> While Len(Range("A" & CStr(LSearchRow)).Value) > 0
>
> 'If value in column L = LSearchValue copy entire row to DAILY
>
> If Cells(LSearchRow, "L").Value = LSearchValue Then
> 'Select row in JobLogEntry to copy
> Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
> Selection.Copy
>
>
> 'Paste row into DAILYOUT in next row
> Sheets("DAILYOUT").Select
> Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
>
> 'With the following line the Conditional Formatting from
> JobLogEntry sheet
> 'is transferred to DAILYOUT SHEET Conditional Formatting Rules
> Manager
> 'which overwrites any rules entered, need a line to preserve the
> rules on DAILYOUT
> 'SHEET
> Range("$A$4:$X$500").FormatConditions.Delete
>
> ActiveSheet.Paste
>
>
> 'Move counter to next row
> LCopyToRow = LCopyToRow + 1
> Rows(LCopyToRow).Insert
>
>
> 'Go back to JobLogEntry to continue searching
> Sheets("JobLogEntry").Select
>
> End If
>
> LSearchRow = LSearchRow + 1
>
> Wend
>
>
> 'Position on cell A last row of DAILYOUT
> Application.CutCopyMode = False
> Sheets("DAILYOUT").Select
> Range("A2").End(xlDown).Select
> ActiveCell.Offset(1, 0).Select
>
>
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
>
> Application.Run "Reset"
>
> Exit Sub
>
> Err_Execute:
> MsgBox "An error occurred."
>
> End Sub
>
>
> Does anyone have a similar problem or have a fix for this? Any help
> will be greatly appreciated!
> Thanks in advance!
> Ken
>
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
