Pull values of a column of one sheet based on dates

[mshahbaz]

Hi,

Sheet 1 (Named as Imports):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]........[/TD]
[TD]........[/TD]
[TD]........[/TD]
[TD]......[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]Material name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Arrival date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 (Named as To dos):

Column D

I need Name of materials (arriving within in next 30 days) with their arrival date in column D of sheet 2.

VBA method is preferable.


Regards[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Option Explicit


Sub Next30()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Imports")
    Set s2 = Sheets("To dos")
    Dim i As Long, lr As Long, lr2 As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lr2 = s2.Range("D" & Rows.Count).End(xlUp).Row
        If s1.Range("G" & i) > Date - 30 Then
            s1.Range("A" & i).Copy s2.Range("D" & lr2 + 1)
        End If
    Next i


End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[mshahbaz]

Thank You

Option Explicit


Sub Next30()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Imports")
    Set s2 = Sheets("To dos")
    Dim i As Long, lr As Long, lr2 As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lr2 = s2.Range("D" & Rows.Count).End(xlUp).Row
        If s1.Range("G" & i) > Date - 30 Then
            s1.Range("A" & i).Copy s2.Range("D" & lr2 + 1)
        End If
    Next i


End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button