Auto Copy/Paste row by row between sheets.

[blastronaut]

I'm sorry if the thread title isn't perfect. I'm having a hard time putting it into words. Here is my problem:

On Sheet3, I have a list of data, 3 columns across and 1300 rows long. It is a list of variables.

I need to find a way to copy the first set of variables, Sheet3(A2:C2) into Sheet2(A2:C2). This will trigger a ton of calculations on Sheet1 that will return a result to Sheet2(D2). I need to then copy Sheet2(D2) to Sheet3(D2) and repeat the process.

The data from Sheet3 will always be copied into the same 3 cells on Sheet2. For example:

Sheet3(A2:C2) is copied into Sheet2(A2:C2). Then, Sheet2(D2) is copied into Sheet3(D2). Next it would be, Sheet3(A3:C3) are copied into Sheet2(A2:C2). Then Sheet2(D2) is copied into Sheet3(D3) and so on.

Is there a way to do this automatically so that I am not copying and pasting for 2 weeks?

[watersev]

hi, blastronaut, can you post sample workbook?

[blastronaut]

hi, blastronaut, can you post sample workbook?

Sure, let me throw something together real quick.

[stnkynts]

Maybe:

Please Login or Register  to view this content.

[blastronaut]

Updated first post with a sample workbook.

[stnkynts]

Try above macro. Should do the trick for you. Im assuming you have some sort of calculation that happens in Sheet2(D2) so i just have the values transferring over to sheet3 not the formula itself.

[blastronaut]

Try above macro. Should do the trick for you. Im assuming you have some sort of calculation that happens in Sheet2(D2) so i just have the values transferring over to sheet3 not the formula itself.

Sorry, I am kinda new to this. What triggers the above macro?

[watersev]

blastronaut, well that's only one part of the story, where are "tons of caclulations"? Copying action to a different sheet to get some result of calculation copy backward is strange. Why not to do that on the same sheet? I'd like to see the whole file if it's possible. If it's not possible I'm off as you have an answer already provided by stnkynts.

[stnkynts]

You have a couple of options. You could set up a button on your worksheet (called a command button) and use it to trigger the macro on click. Or you could put the macro in a module of Visual Basic Editor and set it up to where the macro will run by hotkey (example Ctrl +Shift +S) or you could just tell it to run through the ribbon/file menu at the top. Let me know.

[blastronaut]

blastronaut, well that's only one part of the story, where are "tons of caclulations"? Copying action to a different sheet to get some result of calculation copy backward is strange. Why not to do that on the same sheet? I'd like to see the whole file if it's possible.

I am not at my home computer so I do not have the actual worksheet. Let me try to explain:

Sheet1 takes the data from Sheet2(A2:C2) and runs those 3 values through a bunch of different formulas. The result is one value that is then copied into Sheet2(D2).

I need to know the outcomes for a huge list of different data sets. Those are located on Sheet3. I then need the outcome from each data set pasted into the cell adjacent to that data set so I can later compare them all.

Sheet1 is setup to run all of the calculations and paste the outcome to Sheet2(D2) based on a Worksheet_Change event on Sheet2. So once I paste a set from Sheet3 to Sheet2, Sheet2 changes causing Sheet1 to run it's calculations and paste the result into Sheet2(D2).


Does any of that make sense?

[blastronaut]

You have a couple of options. You could set up a button on your worksheet (called a command button) and use it to trigger the macro on click. Or you could put the macro in a module of Visual Basic Editor and set it up to where the macro will run by hotkey (example Ctrl +Shift +S) or you could just tell it to run through the ribbon/file menu at the top. Let me know.

Is there a way to set it to loop until it runs out of data sets on Sheet3? That way I could just press a command button once and let it work its magic.

[watersev]

@blastronaut, if the dataset is really huge, I'd like to see the workbook with calculations, I think we will avoid those strange copy - paste - calculate - copy operations. If you post requiredfull file, please let me know through private message I will take a look at it.

[stnkynts]

Ahh i see where waterserv is going with this (he is super smart). Doing a loop is easy but at this point it might be best to toss up a copy of your workbook.

[blastronaut]

@blastronaut, if the dataset is really huge, I'd like to see the workbook with calculations, I think we will avoid those strange copy - paste - calculate - copy operations. If you post requiredfull file, please let me know through private message I will take a look at it.

Ahh i see where waterserv is going with this (he is super smart). Doing a loop is easy but at this point it might be best to toss up a copy of your workbook.

I am not really at liberty to post the workbook. Sheet1 contains a formula that I am not allowed to divulge.

At first, Sheet1 was set up to provide an answer for one data set. Later it was decided that we should run multiple data sets (up to 2000) through the same formula and test the results.

Sheet2 and Sheet3 came along as I was trying to retrofit the original file with a way to input variables into the formula on Sheet1.

I'm sorry that I can not post the original workbook. Can you guys still help me?

[watersev]

well it's up to you though I do not think the formula is so exclusive ... anyway please check attachment, open the workbook, run macro "test". It copies data to sheet2, waits for 1 second (can be prolonged if required) for "tons of calculations" to complete and takes the result to sheet3 appropriate D cell.
Without calculation 400 will appear in sheet3. That's all I can do for you then.

PS. It looks you will have plenty of time for a tea and newspaper reading

[blastronaut]

well it's up to you though I do not think the formula is so exclusive ... anyway please check attachment, open the workbook, run macro "test". It copies data to sheet2, waits for 1 second (can be prolonged if required) for "tons of calculations" to complete and takes the result to sheet3 appropriate D cell.
Without calculation 400 will appear in sheet3. That's all I can do for you then.

PS. It looks you will have plenty of time for a tea and newspaper reading

You sir, are my hero. That worked beautifully. I know that my design and requirements aren't the most elegant and I commend you for helping me out anyway.

Thank you so much!