Previous Topic Next Topic

Creating A Summary Sheet

Closed
BuckeyeJR Posts 1 Registration date Friday January 22, 2016 Status Member Last seen January 22, 2016 - Jan 22, 2016 at 11:06 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 23, 2016 at 06:44 PM
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 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 23, 2016 at 06:44 PM
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.