Creating A Summary Sheet [Closed]

Report
Posts
1
Registration date
Friday January 22, 2016
Status
Member
Last seen
January 22, 2016
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!