Need to Copy data and Paste it based on current Date

[dheiland]

I have a problem with my workbook I was hoping someone here may be able to solve. I am trying to copy data (the percentage results of a validation list) from one sheet and then post that data to another sheet. The second sheet has a column of dates and I would like the percentages to paste into the cells that correspond to the current date. Once this is accomplished, I will use the data to plot a chart showing values by date.

I have attached the workbook (sans confidential material) for reference. The workbook contains four sheets, the first two ("Cover Sheet" and "Instructions") can be ignored as they have no functionality or relevance to this situation. The third sheet ("M.A.C. & GoNoGo") contains the validation list that I am trying to plot the data for (the one on the left, under the cell with "status" written in it). The fourth worksheet ("projected") is where I would like the data to be pasted.

I have used a simple reference formula to place the current percentage results of the validation list into a pair of cells [L16:M16] on the fourth sheet (so as to have all necessary data present on the fourth sheet, I figured it would make for easier coding). Those cells are next to a cell [K16] that displays the current date (I though that might be useful for a MATCH or INDEX formula). There are four columns [B through E] that have headers in row 2. The first column ("Date Entered") contains a list of dates, the second column ("Milestone") contains values based on formulas (please ignore this column, it is not relevant to this situation), the third column ("Complete %") is where i would like the data from cell L16 to be pasted, the fourth column ("Not Complete %") is where i would like the data from cell M16 to be pasted. Again, I would like this data to be pasted in the row corresponding to the current date (found in the list of dates in column B under "Date Entered").

I have been attempting (and failing spectacularly) to create a macro to achieve these goals. I had tied the macro to run when a button on sheet 3 is clicked (big gray button right in the middle, has "Chart Data" written on it). I absolutely need the macro to run when that button is clicked, so please keep that in mind if attempting to assist.

I have left the remnant of the seriously butchered code that i took from another workbook and attempted to modify. I don't think it will be helpful, but it might be pretty funny to see.

FYI (as you will be able to tell from looking at the doc) I am not an advanced excel user nor am I very capable at coding VBA. I am very open to and grateful for tips or revisions on the rest of the document too (if any glaring errors/problems are noticed).

Thanks very much in advance,
dheiland

P.S. If any more information is required, please ask and I will do my best to better explain what it is I need.

[rylo]

Hi

See if this does it.

Dim OutSH As Worksheet, outrow As Long
    Set OutSH = Sheets("Projected")
    
    outrow = Evaluate("=match(today(),projected!B:B,0)")
    OutSH.Cells(outrow, "D").Value = Sheets("M.A.C. & GoNoGo").Range("E65").Value
    OutSH.Cells(outrow, "E").Value = Sheets("M.A.C. & GoNoGo").Range("G65").Value

[dheiland]

Hi rylo, thank you for the response. I am just leaving work now, but I will try that first thing tomorrow morning. One question, where should I paste the code you wrote? (sorry, I bet that's a stupid question, but in excel, I am stupid )

dheiland

[rylo]

Hi

This is replacement code for Sub PostData().

rylo

[dheiland]

It appears that that worked, I can't verify that until tomorrow when the current date is different. Assuming it works then, I will mark this thread as solved.

Thank you so much, I have been working on this for a couple of weeks (off and on) and had become frustrated with my lack of progress.

[dheiland]

Alright checked it again and it is working (I meant "Work-tomorrow" (monday) in my previous post). Thanks again for the help, very much appreciated.