I am trying to copy columns A:N from sheets "Deliverables" to worksheet "Next7". I have limited knowledge of VBA, please help. I am also trying to delete the data in Next7 prior to pasting the data.
Post copying data I wish to check if data in column B is "Current Date" +/- 7 AND if column 6 value is pending. I wish to delete the row if criteria is not met. This will give me a list of all pending reports which I intend to use for my scrollable dashboard in home tab (I am clueless on that too). I will delete several columns as for scrollable list, I just need 3 columns.
Please find my code below.
Sub Next7DaysReports()
'
' Next7DaysReports Macro
' Reports pending in the next 7 days
Dim x As Long, lastrow As Long
Worksheets(“Next7”).Range("A:N").ClearContents
Sheets("Deliverables").Active
Range("A:K").Select
Selection.Copy
Sheets("Next7").Range("A:K").Select
ActiveSheet.Paste
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 2).Value >= Date + 7 And Cells(x, 27) = "Pending" Then
Rows(x).Delete
End If
Next x
End Sub
Bookmarks