Loop through two validation lists + copy and paste output

Hi there,

I'm trying to create a macro to automate calculation outputs for different variables. Very new to macro so looking for guidance.

In Sheet1, I have 2 dropdown (data validation) lists (one for name in cell A1, one for date in cell A2). I have a pre-built calculation with 2 different outputs in cell B1 and B2. I want to calculate for each name across all the available dates and copy the outputs in cell B1 and B2 to Sheet1 column C and D that match name in column A and date in column B. Here's the logic I'm trying to do:

1. Select from name list in cell A1 in sheet1
2. Select from date list in cell A2 in sheet1
3. Copy value from B1 and B2 from sheet1 to column C and D in sheet2 that match both name and date in column A and B
4. Select the next date in A2 in sheets and repeat step 3 for all the available dates in the dropdown
5. Once done, select the next name, repeat all the steps above. Repeat for all the names and dates.

Any help is greatly appreciated.

Thank you!

Thank you, Carim. Here's the sample file. Thank you so much for getting back to me.

I could but the there're thousands of rows, and the data will consistently change, so copy and paste isn't very sustainable.

All I'm trying to do is to automate copying output I and II in sheet1 to the sheet2 for each name across all dates. The outputs you currently see are static, but in my actual files, they are different numbers based on a calculation we've built and name/date selected from data validation.

I've included outputs sample for sheet1 and expected results for sheets 2 (essentially trying to fill this with results from cell B1 and B2 in sheet1). Let me know if that still isn't clear. Thank you again for looking into this. I really appreciate it.

Also, I know it can be confusing. The reason why I can't just do a lookup is because the sample outputs I shared in D7:G13 don't actually exist in the sheet. They are the background calculation hence why I'm trying to automate copying B1 and B2 outputs.

I feel like I shared everything I can explain.

1. The source (what I called outputs earlier) are cell B1 and B2

2. The destination are column C and D of sheet2.

So I'm just trying to copy and paste 1. to 2. using Macro.

I found a similar thread that might help understand the issue a bit better.

Loop through Excel drop down list and copy/paste the value

I'm not sure I understand your question.

I think, essentially, the values (depending on name and date selected) will be copied to column C and D in sheet2 based on the matching names and dates in first 2 columns of sheet2. So value I for name A and date 1/1/2021 will be copied to cell C2 of sheet2, and value 2 for name A and date 1/1/2021 will be copied to cell D2 of sheet2.

Yup, if I understand your comment correctly, that's exactly right.

Thank you so much. Glad I was able to help clarify. It's pretty complicated especially not being to see the actual data set and talk live.

All to say, I appreciate it

1. Correct. The calculation will generation inputs for B1 and B2, and the macro will run these inputs for all the possible combos (A1 and A2) and record them at destination.

2. No problem. I think it won't be too bad