Macro to automate copy/paste sequences

[Steven Fleck]

hi all

I have a spreadsheet tool that I use to calculate values on a set of variables, based on values on an initial set of other variables, for a large number of cases. The calculations require a number of sequential steps to be repeated to get the calculated values for each case, and I am hoping there is a straightforward way to use a VB macro to automate this process. However, as a VB beginner I have not been able to find the appropriate way to do this, so would really appreciate any help I could get to make this work!

Essentially, these steps are required to calculate values for a single case:
1. A data input sheet holds the values on the initial/input variables for each case. The first step involves copying the string of values for the case on the input variables into a sheet where the calculations are done.
2. As soon as the input string of values are pasted into the calculation sheet, the calculation formulas generate values for the case on the new, output variables.
3. The output variable string is then copied from the calculation sheet into a “results output” sheet. This copy/paste procedure needs to offset from the first row on the output sheet, so that the row number where the values on the output variables are stored correspond to the case ID. For example, case ID 10 is stored 10 rows down from the first row (i.e., on row 11). Case ID 100 is stored 100 rows down from the first row (i.e., on row 101).

As there are a very large number of cases, I need to have a “control panel” in place, where I can indicate the range of case IDs that will be processed. This takes the form of two cells – one indicates “From ID” and the other “To ID” (for example, “From ID” 1 “To ID” 100). The macro then uses this information to loop through the calculation steps outlined above for these case IDs.

I have attached a spreadsheet file that illustrates the process and structure I am trying to create for this tool.

[StephenR]

If I understand correctly...

Please Login or Register  to view this content.

[Steven Fleck]

Yes, this worked perfectly! Many thanks for your help!!

How can I indicate this post to be solved?

[shg]

To mark a thread as solved:

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

If more than two days have elapsed since your first post, ask a moderator to mark it.

[Steven Fleck]

Have now set this to solved.

However: I use Opera as my default web browser, and on Opera the "Edit" button was not visible. I had to open the thread in Internet Explorer to be able to do this. It might be worth adding a sticky post somewhere to say that some functionality will not work in Opera.