VBA-Copy entire row into another sheet if column K =today()+14

[lunker55]

I want to copy the entire row if, in column K, the date is 14 days from
today into another sheet in the same workbook.
I don't think there is a formula to do this, and the only other way I can
think of is to autofilter to show only the day required and copy/paste into
the other sheet.
I don't really want people doing that!
There are usually 1000 rows totalling 2 months on my sheet.

Any ideas?

Joe

 

[Otto Moehrbach]

You don't say much about the layout of your data nor if the date in Col K
can be 14 days before as well as ahead of today's date.
The following macro will loop through all the used cells in Column K of
the active sheet and copy the entire row of any Column K cell that has a
date equal to or less than14 days ahead of today. You will need to massage
this macro to fit with your data layout and sheet name. HTH Otto
Sub CopyRow()
Dim Rng As Range 'Rng of Col K in Data Sheet
Dim i As Range
Set Rng = Range("K1", Range("K" & Rows.Count).End(xlUp))
For Each i In Rng
If i >= Date And i - Date <= 14 Then
i.EntireRow.Copy
Sheets("Other Sheet").Range("K" & Rows.Count).End(xlUp)(2). _
EntireRow.PasteSpecial
End If
Next i
End Sub

 

[lunker55]

Otto,
Thanks for your help.
I haven't been able to get it to work yet. I know very little about macros.
I copied the macro into my data sheet module and changed "other sheet" to
"shop" which is my destination sheet.
I changed "K1" to "K2" which is my first line of data.
There are 20 columns consisting of names, model numbers, dates etc.
Am I missing something?

Joe

 

[Otto Moehrbach]

Yes. The macro is not a sheet macro and, as such, will not work if you
place it in the sheet module. It must reside in a normal or regular module.
I'm sending you, direct, an otherwise blank file that has the macro in the
proper module. You can contact me direct if you need more. Remove
"cobia97" from my email address. HTH Otto