Writing a Macro

[Krist Cheung]

Dear all Excel Experts,
How are you and hope that you guys can help me on this.

I am trying to write a Macro to do the following.

in Sheet 1 - I have today's date (cell B2).

Then, I would like to create a "Macro Click Button", which will then search the "Date Column" in a report that I have in "Sheet 2".

Macro will then compare today's date and the Date Column in "Sheet 2".

If the Date in "Date Column" in "Sheet 2" is greater than 7 days from today's date, then, copy Column A to Column F, and paste from "Sheet 2" to "Sheet 1".

If the Date in "Date Column" in "Sheet 2" is shorter than 7 days from today's date, then do NOT copy.

does it make sense?

It is much appreciated if someone can help me on this macro.

Kind regards,

Krist

 

[Peter_SSs]

Quite a few people have looked at your question but not answered so I'm guessing they do not understand either what you have or what you want.
I suggest providing with XL2BB (see below) small samples from the two sheets with "before" and "after" samples where relevant and explain again in relation to the specific samples you have provided.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MikeVol]

Hi @Krist Cheung. I don't know if I understood you correctly. Here's an option for you:

Option Explicit

Sub CopyIfDateGreaterThan7Days()
    Dim i           As Long

    Dim wsSource    As Worksheet
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")

    Dim wsDest      As Worksheet
    Set wsDest = ThisWorkbook.Worksheets("Sheet1")

    Dim todayDate   As Date
    todayDate = wsDest.Range("B2").Value

    Dim lastRow     As Long
    lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

    Application.ScreenUpdating = False
    wsDest.Range("F2:F" & wsDest.Cells(wsDest.Rows.Count, "F").End(xlUp).Row).ClearContents

    Dim copyRow     As Long
    copyRow = 2

    For i = 2 To lastRow

        If wsSource.Cells(i, "B").Value > todayDate + 7 Then
            wsDest.Cells(copyRow, "F").Value = wsSource.Cells(i, "A").Value
            copyRow = copyRow + 1
        End If

    Next i

    Set wsDest = Nothing
    Set wsSource = Nothing
    Application.ScreenUpdating = True
    MsgBox "Copying complete. Check Column F in Sheet1."
End Sub

Good luck.