Dear Sir,

We are really thankful to you for uploading such important videos on YouTube. We have a workbook called DayBook where we enter the daily transactions. We would like to extract all rows that contain the entry ‘sales’ and copy them to a ‘mastersales’ workbook. Also we would like to update our ‘mastersales’ workbook on a daily basis so that only ‘sales of today’ are posted to the ‘mastersales’ workbook. How can we achieve this?

Thanks & Regards

Jain & Co.

What Jain wants to do is the following:

1. Identify data in a workbook sheet based on a text criterium and a date criterium or condition using a looping process
2. Select the specific data which meets the two or multiple conditions
3. Copy the identified data
4. Open another workbook
5. Find the first blank row in a specific worksheet in the workbook
6. Paste the data in the identified blank or empty row – erow
7. Save the workbook
8. Close the workbook

The VBA code given below does the job quickly and easily by identifying the relevant rows and then transferring them to another relevant workbook:

Sub mySales()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy

Workbooks.Open Filename:=”C:\Users\takyar\Documents\salesmaster-new.xlsx”
Worksheets(“Sheet1”).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i
End Sub

Watch the training video below:

Download an Excel sample file for pratcice:

Further reading:
Excel VBA Programming Loops