VBA copy and paste rows based on data (Date)

[Rogue909]

I need to copy calibration data from a master calibration file into a report file (to display the daily calibration in the report).

My goal is to put a button on the report excel sheet that will search the daily calibration for all the data that has been entered that day. (The date will be contained in a cell for referencing purposes). Then copy the row and insert it into the report file.

I can "think out" the code in my head. I was thinking of using a for loop to begin searching the dates (In my case the first date is located in cell A26.) Then have it add 1 to each as it searches the rows (A27, A28, A29, etc.) If it finds the day matches the current day then it copies the row (I can also just use offset/range to copy just columns A-P) and paste it into the report file. Then continue to find the rest of the dates that match.

The problem is twofold; I don't know how to stop the For loop (once it finishes sorting dates I don't want it to just continue searching empty cells) and I'm not very good at starting from scratch in VBA (I've only used VBA for 2 weeks maybe? And I'm rather new at coding to begin with...)

Does anyone have an example of a code that runs a similiar task that could help me?
Or know if an easy solution to this?
Any advice in general?

 

[Truiz]

You can try:

Sub VCAPRBODD()Dim lRow As Long
Dim iCntr As Long


Application.ScreenUpdating = False


lRow = Sheets("Master").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
  For iCntr = 26 To lRow Step 1
    If Cells(iCntr, "A") = Sheets("Master").Range("A23") Then
     Rows(iCntr).Copy Sheets("Other").Rows(Sheets("Other").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1)
    End If
  Next
  
Application.ScreenUpdating = True


End Sub

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
22	Reference Date
23	1-Jan-17
24
25	Date	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data
26	1/1/2017	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1
27	1/2/2017	Data Col B 2	Data Col C 2	Data Col D 2	Data Col B 2	Data Col C 2	Data Col D 2	Data Col B 2	Data Col C 2	Data Col D 2	Data Col B 2	Data Col C 2	Data Col D 2	Data Col B 2	Data Col C 2	Data Col D 2
28	1/1/2017	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3
29	1/2/2017	Data Col B 4	Data Col C 4	Data Col D 4	Data Col B 4	Data Col C 4	Data Col D 4	Data Col B 4	Data Col C 4	Data Col D 4	Data Col B 4	Data Col C 4	Data Col D 4	Data Col B 4	Data Col C 4	Data Col D 4
30	1/1/2017	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5
31	1/2/2017	Data Col B 6	Data Col C 6	Data Col D 6	Data Col B 6	Data Col C 6	Data Col D 6	Data Col B 6	Data Col C 6	Data Col D 6	Data Col B 6	Data Col C 6	Data Col D 6	Data Col B 6	Data Col C 6	Data Col D 6
32	1/3/2017	Data Col B 7	Data Col C 7	Data Col D 7	Data Col B 7	Data Col C 7	Data Col D 7	Data Col B 7	Data Col C 7	Data Col D 7	Data Col B 7	Data Col C 7	Data Col D 7	Data Col B 7	Data Col C 7	Data Col D 7
33	1/1/2017	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8
34	1/1/2017	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9
35	1/3/2017	Data Col B 10	Data Col C 10	Data Col D 10	Data Col B 10	Data Col C 10	Data Col D 10	Data Col B 10	Data Col C 10	Data Col D 10	Data Col B 10	Data Col C 10	Data Col D 10	Data Col B 10	Data Col C 10	Data Col D 10
36	1/1/2017	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11
37	1/3/2017	Data Col B 12	Data Col C 12	Data Col D 12	Data Col B 12	Data Col C 12	Data Col D 12	Data Col B 12	Data Col C 12	Data Col D 12	Data Col B 12	Data Col C 12	Data Col D 12	Data Col B 12	Data Col C 12	Data Col D 12
38	1/2/2017	Data Col B 13	Data Col C 13	Data Col D 13	Data Col B 13	Data Col C 13	Data Col D 13	Data Col B 13	Data Col C 13	Data Col D 13	Data Col B 13	Data Col C 13	Data Col D 13	Data Col B 13	Data Col C 13	Data Col D 13
39	1/1/2017	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14
40	1/3/2017	Data Col B 15	Data Col C 15	Data Col D 15	Data Col B 15	Data Col C 15	Data Col D 15	Data Col B 15	Data Col C 15	Data Col D 15	Data Col B 15	Data Col C 15	Data Col D 15	Data Col B 15	Data Col C 15	Data Col D 15
41	1/1/2017	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16
42	1/3/2017	Data Col B 17	Data Col C 17	Data Col D 17	Data Col B 17	Data Col C 17	Data Col D 17	Data Col B 17	Data Col C 17	Data Col D 17	Data Col B 17	Data Col C 17	Data Col D 17	Data Col B 17	Data Col C 17	Data Col D 17
43	1/1/2017	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18
Master

Will result in:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data	Other Data
2	1/1/2017	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1	Data Col B 1	Data Col C 1	Data Col D 1
3	1/1/2017	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3	Data Col B 3	Data Col C 3	Data Col D 3
4	1/1/2017	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5	Data Col B 5	Data Col C 5	Data Col D 5
5	1/1/2017	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8	Data Col B 8	Data Col C 8	Data Col D 8
6	1/1/2017	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9	Data Col B 9	Data Col C 9	Data Col D 9
7	1/1/2017	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11	Data Col B 11	Data Col C 11	Data Col D 11
8	1/1/2017	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14	Data Col B 14	Data Col C 14	Data Col D 14
9	1/1/2017	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16	Data Col B 16	Data Col C 16	Data Col D 16
10	1/1/2017	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18	Data Col B 18	Data Col C 18	Data Col D 18
Other

 

[Fluff]

An alternative option is

Sub CopyOnDate()

    With Sheets("[COLOR=#ff0000]Master[/COLOR]")
        With .Range("A26", .Range("A" & Rows.Count).End(xlUp))
            .Replace Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("A23").Value, True
            On Error GoTo Xit
            .SpecialCells(xlConstants, xlLogical).EntireRow.Copy Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
            On Error GoTo 0
            .Replace True, Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("A23").Value
        End With
        Sheets("[COLOR=#ff0000]List[/COLOR]").Columns(1).Replace True, .Range("A23").Value
    End With
Exit Sub

Xit:
    MsgBox "No dates found"
    
End Sub

Changing sheet names to suit