Keep the conditional formatting from changing the apply-to range

Question

I am using this code to copy/paste (by keeping value & formating) from sheets ending in -A & -B into one sheet Target Sheet:

Sub Merge_AllSheets_into_One()
   Dim Sheet As Worksheet
   Dim TargetRow As Long
   'Application.ScreenUpdating = True
   Sheets("Target Sheet").Range("A3:FN10000").Cells.clear
   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False

   TargetRow = 3
   For Each Sheet In ActiveWorkbook.Sheets
      If Sheet.Name Like "*" & strSearch & "-A" Or _
         Sheet.Name Like "*" & strSearch & "-B" Then
         Sheets(Sheet.Name).Range("AA3:GN90").Copy
         With Worksheets("Target Sheet").Cells(TargetRow, 1)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
         End With
         TargetRow = TargetRow + 88
      End If
   Next
   Application.CutCopyMode = False

End Sub

The copied data are of the same size, same range across different sheets.
The problem is that the Target Sheet contains quite a number of conditional formatting rules that get changed upon each merging.
That is, every time I merge the ranges into Target Sheet, it removes the old data (that is what I want, either remove or replace the old data), and pastes the new data under one another.

How to copy/paste (=merge) the data into Target Sheet without changing the conditional formatting apply-to ranges?

Update

Actually the range that I am copying from different sheets have the same range, each range is 88 rows ("AA3:GN90"). They contain merged cells: the merged cells are the first four columns of each range ONLY:

- AA3:AA90
- AB3:AB90
- AC3:AC90
and four rows by four rows are merged in column AD
AD3:AD6
AD7:AD10
and so on until AD87:AD90
When copy/pasting into the target sheet, I want to keep these merged cell as they are originally, and also keep the conditional formatting rules in the target sheet.

A quick note: some cells in these sheets also contain font coloring and cell coloring. I would like to keep them as well, if possible. If not, they this condition can be omitted.

More info on the sheet and the rules: there are more than 30 conditional formatting rules in the target sheet. So each time I clear the cells when importing the new data from other sheets, the formatting ranges keep moving on and changing. It is not possible for me to rewrite these rules, because for the reasons of data control and repeated use of this VBA code, I need to run this code more than 10 times (data controlling reasons) for each data set and see where the codes apply. It is practically impossible to spend the day to rewrite the ranges for these rules.

I would try to disable events. Don't forget to reenable them at the end of your sub. — user3819867

Unknown Unknown · Accepted Answer · 2015-05-03T20:16:05

Some of this will depend upon what version of Excel you are using. As Excel 2010 is currently the predominant version, I'll conform to that standard.

If you are trying to retain the CF rules and Applies to: on the target worksheet, you shouldn't be using the .Clear (aka Clear All) command. Clearing the values/formulas only should be sufficient and you can bring the number formatting across in your .PasteSpecial to overwrite existing cell number formats.

Sub Merge_AllSheets_into_One()
    Dim ws As Worksheet
    Dim TargetRow As Long

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    'only clear the contents - CF stays and cell number formats will be overwritten
    'why clear AA:FN if you are copying AA:GN below?
    Sheets("Target Sheet").Range("A3:FN10000").Cells.ClearContents

    TargetRow = 3
    For Each ws In ActiveWorkbook.Sheets
        With ws
            If .Name Like "*" & strSearch & "-A" Or _
              .Name Like "*" & strSearch & "-B" Then
                'are there ALWAYS 88 rows to copy?
                .Range("AA3:GN90").Copy
                With Worksheets("Target Sheet").Cells(TargetRow, 1)
                    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'this is a newer Paste Special option
                End With
                TargetRow = TargetRow + 88
            End If
        End With
    Next ws

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

This may or may not be appropriate depending upon what formats you are trying to bring across. If you want anything other than number formatting like manual cell/row highlighting, it would be best to simply wipe the slate clean with .Clear, copy and paste then redefine the CF rule's Applies to: before moving on to the next worksheet in the cycle.

Keep the conditional formatting from changing the apply-to range

Update

2 Answers