基于条件格式红色单元格批量复制粘贴至其他工作表的技术咨询

Hey there! Let's work through how to copy those red cells (the ones with a deviation ≥25% from their target value) over to another worksheet. Since the red formatting is tied to specific deviation rules, we’ve got two reliable approaches to get this done:

Method 1: Automate with VBA #

This is perfect if you want to skip manual work and repeat the task easily later. Here’s the step-by-step:

1. Open your Excel file, press Alt + F11 to launch the VBA Editor.
2. Right-click your workbook in the Project Explorer panel > Insert > Module.
3. Paste this code into the module:

Sub CopyRedCells()
    Dim sourceWS As Worksheet, targetWS As Worksheet
    Dim sourceRange As Range, cell As Range
    Dim targetRow As Integer
    
    ' Update these sheet names to match your workbook
    Set sourceWS = ThisWorkbook.Worksheets("SourceSheet")
    Set targetWS = ThisWorkbook.Worksheets("TargetSheet")
    
    targetRow = 1
    ' Copy header row first (optional but helpful)
    sourceWS.Range("A1:D1").Copy targetWS.Range("A1:D1")
    targetRow = targetRow + 1
    
    ' Define the range to scan (adjust based on your data size)
    Set sourceRange = sourceWS.Range("A2:D" & sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In sourceRange
        ' Skip the Target column (column B) since we don't need to check it
        If cell.Column <> 2 Then
            Dim targetVal As Variant, cellVal As Variant
            targetVal = sourceWS.Cells(cell.Row, "B").Value
            cellVal = cell.Value
            
            ' Calculate absolute deviation percentage
            Dim deviation As Double
            If targetVal <> 0 Then ' Avoid division by zero errors
                deviation = Abs(cellVal - targetVal) / targetVal
                ' Check if it meets the red condition (≥25% deviation)
                If deviation >= 0.25 Then
                    ' Copy value to target sheet
                    targetWS.Cells(targetRow, cell.Column).Value = cellVal
                    ' Optional: Copy the red fill color too
                    targetWS.Cells(targetRow, cell.Column).Interior.Color = cell.Interior.Color
                End If
            End If
        End If
    Next cell
    
    ' Clean up target sheet formatting
    targetWS.Columns.AutoFit
    MsgBox "Red cells copied successfully!", vbInformation
End Sub

4. Replace "SourceSheet" and "TargetSheet" with your actual sheet names.
5. Press F5 to run the macro, or assign it to a button for one-click access later.

Method 2: Manual Approach with Helper Columns & Filters #

If you’d rather avoid code, use formulas to flag red cells and filter them:

1. Add helper columns (e.g., E, F, G) next to your data. In cell E2, enter this formula:

   =ABS(C2-$B2)/$B2
   

   Drag this formula across to columns F and G (for Feb and Mar), then drag down to cover all rows. This calculates the absolute deviation percentage from the target.
2. Select your entire data range (including headers and helper columns), then go to Data > Filter.
3. Click the filter arrow on each helper column (E, F, G) > Number Filters > Greater Than Or Equal To, then enter 0.25.
4. Select the visible cells (use Ctrl + G > Special > Visible cells only to avoid hidden rows), copy them, and paste into your target worksheet.

Both methods work for both numerical and percentage values since the deviation calculation handles both types uniformly.

内容的提问来源于stack exchange，提问作者rockstar