Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
This is really too much for one question. The file stuff should be separate from the matching stuff. –  Lance Roberts Nov 1 '10 at 20:29
add comment

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
    
@ Xantec... Thank you. Could you show me how the Vlookup would look? –  edmon Nov 1 '10 at 20:53
    
edited my answer to include the VLOOKUP –  Xantec Nov 1 '10 at 21:06
    
What if there are a total of 7 columns with data for the coloumn index number? –  edmon Nov 1 '10 at 21:15
    
re: VLOOKUP; In the complete range of data the first column has to contain the values you are comparing your look up value against. if there are a total of 7 columns of data in the complete range of data and you want the values in the seventh column then change the 2 to a 7 –  Xantec Nov 1 '10 at 21:59
    
@Xantec thank you for you help. I just have a couple of questions. Set TW = Thisworkbook and Set EW = Activeworkbook, do I need to replace those with the actual name of the workbooks? And where you have value = "", what should go between the quotations? –  edmon Nov 1 '10 at 22:04
show 11 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.