Creating a macro to copy data and paste to another workbook

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

-
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

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
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
``````
-
@ 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