Copy data from one sheet to another conditionally in Excel VBA

In my excel sheet, I've 2 sheets.

1. Name as "Master Sheet"
2. Q1

In this article we will copy only those rows to sheet "Q1" where Quarter column contains text Q1.

So to copy those rows

Follow the below steps.

1. Open your excel file.
2. Press Alt + F11 to open VBA Editor
3. Insert the module as shown in snapshot below

 

4. Create the function in the module.

Sub Quarter()
   
    Dim xRg As Range
    Dim xCell As Range
    Dim i As Long
    Dim Q1Count As Long
   
    i = Worksheets("Master Sheet").UsedRange.Rows.Count
    Q1Count = 1
   
    Set xRg = Worksheets("Master Sheet").Range("D1:D" & i)
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each xCell In xRg
   
        If CStr(xCell.Value) = "Q1" Then
            xCell.EntireRow.Copy Destination:=Worksheets("Q1").Range("A" & Q1Count + 1)
            Q1Count = Q1Count + 1
        End If
     
    Next
    Application.ScreenUpdating = True
   
End Sub

In this function I've implemented foreach, If cell contains text Q1, then that row will be copied to Destination file.

5.  Save the file as macro enabled workbook. (save as .xlsm format)
6. Press F5 to run the function.

If you want your rows to copy automatically when you press Ctrl + S then implement below code in workbook file. Make sure you are not writing this code on Module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

  Quarter

End Sub

Once you press save or Ctrl + S, your rows will copy automatically.


If you want your rows to copy automatically when you change anything in excel then implement below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Quarter
End Sub

If you want your rows to copy on button click then add button on your excel file.

To add the button, go to Developer menu, Press Insert and select button from Form controls as shown in snapshot below and draw rectangle on page where you want button.




   You can see button in below snapshot.

You can change the button text by pressing right click on the button and you can also give the button Ids as well.
Now we need to add click event of button.

Implement below code in workbook file. Make sure you are not writing this code in Module file.

Private Sub Button2_Click()

  Quarter

End Sub

Once you press button, it will copy the excel file rows to sheet Q1.


If you want to delete all the content of sheet Q1 before copy anything then call below method before calling Quarter method.

Sub DeleteRecords()

      Sheets("Q1").Cells.Delete    

End Sub

This method will delete all the content of sheet Q1.


That's all about the copy and paste. If you have any question you can write on the blog.