Excel: IF Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition

Download the featured file here. Please note I cannot upload macro-enabled workbooks to WordPress. The VBA code is provided below.
https://www.bluepecantraining.com/wp-content/uploads/2021/04/If-True-Copy.xlsx

Need pre-built Excel spreadsheet solutions?  Try Simple Sheets - over 100 templates covering your personal and work-based needs including Gantt charts, personal budgets, employee scheduling, timesheets and more… https://www.simplesheets.co/a/45451/v7Aj9zMx

By clicking on this link you are helping to support this blog – thanks!

In this video I demonstrate how to copy records that meet specified criteria to another worksheet. I demonstrate three methods:

1) Use filters in Excel and copy/paste visible records
2) Use the FILTER function - available in Excel 365
3) Use a VBA macro - code below

Sub CopyOverBudgetRecords()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range

'You will need to adapt this code for your scenario - follow the STEPS below
'STEP1: Change the sheet name and range in the line of code below to match the sheet name
'and range holding the data that you want to copy rows from.
Set StatusCol = Sheet1.Range("E2:E14")

For Each Status In StatusCol

'STEP2: Change the sheet name and range in the lines of code below to match the sheet name
'and cell that you want to copy your data to. You only need to specify one cell -
'the first cell you will copy to.
If Sheet5.Range("A2") = "" Then
Set PasteCell = Sheet5.Range("A2")
Else
'STEP3: In the line of code below, the range should refer to your first column heading
Set PasteCell = Sheet5.Range("A1").End(xlDown).Offset(1, 0)
End If
'STEP4: I have included three ways of copying the data. To use one of the methods, delete
'the apostrophe before the words IF Status at the beginning of the line.
'You can only use one of the options. The third option is currently active.

'This one was used in the video, but will only work if your criteria is in column 5
'and you have five cells per record
'If Status = "Over budget" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell

'This one copies the entire row - right across the worksheet
'If Status = "Over budget" Then Status.EntireRow.Copy PasteCell

'This one only copies the relevant cells, rather than the entire row and it doesn't
'matter which row contains the criteria or how many cells you need to copy.
'It won't work, however, if you have blank cells in your data.
If Status = "Over budget" Then Range(Status.End(xlToLeft), Status.End(xlToRight)).Copy PasteCell
Next Status

End Sub

Видео Excel: IF Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition канала Chester Tugwell