Copy data from first sheet to other in Excel

Closed
CabelaRay Posts 2 Registration date Thursday December 13, 2018 Status Member Last seen December 13, 2018 - Updated on Dec 13, 2018 at 10:04 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 18, 2018 at 11:35 AM
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.

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 13, 2018 at 11:15 AM
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
0
CabelaRay Posts 2 Registration date Thursday December 13, 2018 Status Member Last seen December 13, 2018
Dec 13, 2018 at 03:34 PM
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
0
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!

0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 18, 2018 at 11:35 AM
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

0