Copy rows from sheet1 to sheet2 based on date.

R

Roodoc

New Member
Joined
Dec 28, 2018
Messages
13
I have a worksheet similar to below, but with many more rows. What I would like to do is to take the information only from the columns ID, Name, and Due Date to the second worksheet when the due date is between 6 months (+190 days) and 2 months (+70 days) from today's date.

[TABLE="class: s90z9tc-19 iDFCDm"]
<thead>[TR="class: s90z9tc-20 eCOxQD"]
[TH="class: s90z9tc-25 cmsMXn"][/TH]
[TH="class: s90z9tc-25 cmsMXn"][/TH]
[TH="class: s90z9tc-25 cmsMXn"][/TH]
[TH="class: s90z9tc-25 cmsMXn"][/TH]
[TH="class: s90z9tc-25 cmsMXn"][/TH]
[TH="class: s90z9tc-25 cmsMXn"]

<colgroup><col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:6912;width:142pt" width="189"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:6912;width:142pt" width="189"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> </colgroup><tbody>
[TD="class: xl71, width: 57"] #[/TD]
[TD="class: xl73, width: 80"]ID[/TD]
[TD="class: xl72, width: 189"]Name[/TD]
[TD="class: xl72, width: 57"]Flags [/TD]
[TD="class: xl72, width: 52"]Code[/TD]
[TD="class: xl72, width: 189"]Classification[/TD]
[TD="class: xl72, width: 87"]Item Date[/TD]
[TD="class: xl76, width: 68"]Due Date[/TD]

[TD="class: xl66, align: right"]101[/TD]
[TD="class: xl66, align: right"]5563[/TD]
[TD="class: xl67"]J. Smith[/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]Y[/TD]
[TD="class: xl67"]Office 13[/TD]
[TD="class: xl70, align: right"]11/15/2018[/TD]
[TD="class: xl74"]2/7/2019[/TD]

[TD="class: xl68, align: right"]102[/TD]
[TD="class: xl68, align: right"]1257[/TD]
[TD="class: xl65"]B. Wallace[/TD]
[TD="class: xl65"]V[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Cube 7[/TD]
[TD="class: xl69, align: right"]8/1/2018[/TD]
[TD="class: xl75"]9/15/2019[/TD]

[TD="class: xl68, align: right"]201[/TD]
[TD="class: xl68, align: right"]259[/TD]
[TD="class: xl65"]M. Salinas[/TD]
[TD="class: xl65"]NF[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Cube 8[/TD]
[TD="class: xl69, align: right"]1/4/2016[/TD]
[TD="class: xl75"]1/7/2019
[/TD]

[TD="class: xl68, align: right"]202[/TD]
[TD="class: xl68, align: right"]3002[/TD]
[TD="class: xl65"]K. Shea[/TD]
[TD="class: xl65"]AV[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Cube 11[/TD]
[TD="class: xl69, align: right"]1/6/2019[/TD]
[TD="class: xl75"]6/28/2021
[/TD]

</tbody>
[/TH]
[/TR]
</thead><tbody>[TR="class: s90z9tc-20 eCOxQD"]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[/TR]
[TR="class: s90z9tc-20 eCOxQD"]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[TD="class: s90z9tc-21 jruhvq"][/TD]
[/TR]
</tbody>[/TABLE]
In the second worksheet (sample below) I would have to add further information in each row. I would like these rows to stay intact when updated, if possible. This is because I have other information to add to the reports that are due within 190 days. As I get the steps completed I could add the dates in.


<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> </colgroup><tbody>
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl70, width: 71"]Name[/TD]
[TD="class: xl71, width: 79"]Due Date[/TD]
[TD="class: xl70, width: 75"]Progress[/TD]
[TD="class: xl72, width: 72"]Referral[/TD]
[TD="class: xl72, width: 81"]Template[/TD]
[TD="class: xl70, width: 64"]Score[/TD]
[TD="class: xl70, width: 64"]RRG[/TD]
[TD="class: xl73, width: 89"]Comments[/TD]

[TD="class: xl67, align: right"]5563[/TD]
[TD="class: xl68"]J. Smith[/TD]
[TD="class: xl74"]5/17/2019
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]
[/TD]

[TD="class: xl67, align: right"]259[/TD]
[TD="class: xl68"]M. Salinas[/TD]
[TD="class: xl74"]6/3/2019
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"] [/TD]

</tbody>


Also, the same information needs to be gathered into a third sheet if it is within 2 months (+70 days) but no longer on the second sheet. With further information gathered there as well.


<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl70, width: 71"]Name[/TD]
[TD="class: xl71, width: 79"]Due Date[/TD]
[TD="class: xl70, width: 75"]Placement[/TD]
[TD="class: xl72, width: 84"]Assessment[/TD]

[TD="class: xl66, align: right"]5563[/TD]
[TD="class: xl67"]J. Smith[/TD]
[TD="class: xl68"]4/13/2019[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl66, align: right"]259[/TD]
[TD="class: xl67"]M. Salinas[/TD]
[TD="class: xl68"]5/15/2019[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]
[/TD]

</tbody>


I have looked into VBAs and such and can not figure them out for the life of me. I can't figure out how to have this data entered without screwing with the rows I already have entered in sheet 2 and 3. This is a worksheet that I use daily. So I would like for the data to be updated each time I view the worksheet. I have tried advanced filters to no avail. Many thanks to those who attempt to help and know much more than I do.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Please help. I have tried macros, filters, advanced filters, and linking cells. I have also tried to watch many hours of VBA videos and have not found how to complete this function.

It would also be helpful if somebody could point me in the direction that could help me understand how to write the VBA for this.
 
Upvote 0
Not sure about the rest of your requirements but the macro below for me with the data as below

Excel Workbook
ABCDEFGH
1#IDNameFlagsCodeClassificationItem DateDue Date
21015563J. SmithAYOffice 1311/15/201807/04/2019
31021257B. WallaceVYCube 708/01/201815/09/2019
4201259M. SalinasNFNCube 801/04/201607/01/2019
5213316Z. MeBOffice 1106/06/201803/06/2019
62023002K. SheaAVYCube 1101/06/201928/06/2021
Sheet1


Changes

Excel Workbook
ABC
1IDNameDue Date
2
3
4
5
6
Sheet2


to

Excel Workbook
ABC
1IDNameDue Date
25563J. Smith07/04/2019
3316Z. Me03/06/2019
Sheet2


on Sheet2.

Please note that my dates are in dd/mm/yyyy format not mm/dd/yyyy format

Code: 
Sub Filterit()
    With Sheets("Sheet1").Range("A1:H" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=8, Criteria1:= _
        ">=" & CLng(Date + 70), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 190)
        
        On Error Resume Next
        Intersect(Sheets("Sheet1").Range("B:C,H:H"), .Offset(1).Resize(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).Copy _
                Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        .AutoFilter
    
    End With
End Sub

Hopefully it will get you started :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
pulling data from one chart to another
Replies
1
Views
811
jb00976
J
A
  • Question Question
exel 2002 total accumulated payments
Replies
4
Views
726
anon125
A
O
  • Question Question
Survey data to capture full month
Replies
5
Views
879
DanteAmor
DanteAmor
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier
D
  • Question Question
VBA - Split Source file-Find oldest date in column. Copy all rows with date to new sheet, Repeat on next oldest date. Also, remove some unwanted data.
Replies
0
Views
1K
DK643
D

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top