Creating A Summary Sheet Closed

Question

Hello,

I have a worksheet that is recording customer interactions, and I would like to create a seperate sheet to summarize all the customers that have not had activity in the past 7 or more days.  

On the original sheet Column A is the date of the last activity; I would like to take the information for that customer (A2-E2) and copy and paste them on sheet 2, or my summary sheet so that I can see those are the customers that need to be contacted today.  

Thank you in advance for your time and help
-JR

vcoolio · Answer

Hello BuckeyeJR,

Perhaps the following code may help:-


Sub CustomerAction()

Application.ScreenUpdating = False

Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

Sheet2.UsedRange.Offset(1).ClearContents

For Each cell In Range("A1:A" & lr)
          If cell <= [Today()] - 7 Then
          cell.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End If
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select

End Sub

The code does as you ask and following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/r3ktosbh4k2c7n1/BuckeyeJR.xlsm?dl=0

Click on the button to see it work.

I'm not sure if you wanted the data in sheet 1 cleared after the transfer of data to sheet 2 so the code just refreshes the data in sheet 2 meaning that all data in sheet 1 will remain but in sheet 2, the data will show old and new entries. Let us know if you need to change this part.

I hope that this helps.

Cheerio,
vcoolio.

Creating A Summary Sheet

1 response

Similar discussions

Subscribe To Our Newsletter!