Creating a macro to copy data and paste to another workbook

Question

I have two workbooks. One is sent via email and has a chart with a column populated with the dates i.e. 11/01/10 through 11/30/10. For that day the chart is populated with a row of data.

The second workbook has a chart with a column populated with the dates as well. What I need help with is creating a macro that will look at the date from workbook2 and match that date with workbook1, sent via email, and copy the row of data from workbook1 and paste the row to workbook2 on the row that has the same date. Never created a macro, so any help would be greatly appreciated

Answer 1

I would imagine that a VLOOKUP formula would be easier than a macro in this instance. After filling the formula down the column do a COPY & PASTE.Values to remove the formula.

Edited in VLOOKUP example: You will need to make a table of the dates in your workbook identical to the dates in the emailed workbook. adjust the following to fit your situation

A1 = is the look up value (date?) from the table where you want the values in the email
[Example.xlsx] = is the name of your emailed workbook
Sheet1! = the name of the spreadsheet in the emailed workbook that has the table of data
$A$1:$B$30 = the complete range of the data in the emailed workbook
2 = the column in the range of data that we want to get the return value from (column 2 in A and B)
FALSE = we want an exact match of the look up value in the email data

=VLOOKUP(A1,[Example.xlsx]Sheet1!$A$1:$B$30,2,FALSE)

However, in order to keep it simple if you are not above keeping one workbook as a template for receiving the data then the following will work. Just place it into a module of the template workbook and save it. When you receive a new email open the template, open the email workbook and then activate the macro from the email workbook

Assumptions in the code:
1: In the emailed workbook the data starts in cell A1
2: In the macro/template workbook the data starts in cell A1
If either of these assumptions are incorrect then adjust the start values for L1 and/or the Cells objects (the first value, L1, is the row and the second number is the column; A = 1)

Sub CopyData()
    Dim All As New Collection
    Dim One As Variant, L1 As Integer, L2 As Integer
    Dim TW As Workbook, EW As Workbook

    Set TW = ThisWorkbook
    Set EW = ActiveWorkbook

    L1 = 15
    Do Until Cells(L1, 2).Value = ""
        ReDim One(0 To 1)
        One(0) = Cells(L1, 2).Value
        One(1) = Cells(L1, 3).Value
        All.Add One
        Erase One
        L1 = L1 + 1
    Loop

    TW.Activate
    L1 = 15
    Do Until Cells(L1, 2).Value = ""
        For L2 = 1 To All.Count
            One = All(L2)
            If One(0) = Cells(L1, 2).Value Then
                Cells(L1, 3).Value = One(1)
                Erase One
                Exit For
            Else
                Erase One
            End If
        Next L2
        L1 = L1 + 1
    Loop
End Sub