Copy data from first sheet to other in Excel

Posts
2
Registration date
Thursday December 13, 2018
Last seen
December 13, 2018
-
I have a workbook for tracking service customers. I would like for some of the data entered in the first sheet (first name, last name, date & email address) to be automatically entered into another for follow up calls.

Not every row in the first sheet contains all this data, so I only need it from those which do.

The sheets are edited several times daily, but the second sheet can be added to on the fly or at the end of the day, whichever is less complicated.
See more 

Your reply

3 replies

Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400
0
Thank you
Hi Cabela,

Some questions arise:
What are your sheets called?
Which rows and columns do you want to copy to the second sheet?
Is the layout for both sheets the same? If not, what is the difference?
What is the criteria for copying the rows (how to know which rows to copy/paste)?

Best regards,
Trowa
CabelaRay
Posts
2
Registration date
Thursday December 13, 2018
Last seen
December 13, 2018
-
Hello Trowa,

1 - Service Spreadsheet
2 - Columns: A – WORK ORDER, C - FIRST NAME, D - LAST NAME, O – PU DATE, Q – EMAIL ADDRESS from the rows that meet the criteria listed
3 - No, the second sheet only contains the 5 listed columns plus one more.
4 - When there is an entry in columns A, C, O and Q I need it added to the second sheet.

A second part to this is that I need to send an email (address in column Q) to these people 7 days after the date in column O. I’d like this to be sent automatically, or as close as possible to automatic. There would be placeholders in the emails for FIRST NAME and WORK ORDER I would like populated from the second sheet, as well as the EMAIL ADDRESS and the subject line for the email to read: Cabela’s Service Department Follow Up.

This is a lot to ask! My skills are minimal at best, so any help at all will be greatly appreciated.

Ray
Respond to TrowaD
Posts
11177
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 21, 2019
1902
0
Thank you
If you are TRACKING calls or work, use a DATABASE. Excel is for calculations, not for storing data. If your coding skills are very limited, then the learning curve to a whole new solution is not that far away, as both skills would be minimum. At least this way, you are using the correct tool for the job, and it SCALES, and once you have a DB, you can write all kinds of reports, that EXCEL WILL NOT DO FOR YOU!

Respond to ac3mark
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400
0
Thank you
Hi Cabela,

For the first part you can try this code:
Sub RunMe()
Dim mFind As Range
Dim lRow As Integer

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Columns("A").Find(what:=cell.Value)
    If mFind Is Nothing Then
        If Range("C" & cell.Row) <> vbNullString And _
        Range("O" & cell.Row) <> vbNullString And _
        Range("Q" & cell.Row) <> vbNullString Then
            With Sheets("Sheet2")
                lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
                .Range("A" & lRow) = cell.Value
                .Range("C" & lRow) = cell.Offset(0, 2).Value
                .Range("D" & lRow) = cell.Offset(0, 3).Value
                .Range("O" & lRow) = cell.Offset(0, 14).Value
                .Range("Q" & lRow) = cell.Offset(0, 16).Value
            End With
        End If
    End If
Next cell
End Sub


I can't help you with the second part as I don't have acces to an e-mail program to play around with.

Best regards,
Trowa

Respond to TrowaD