|We're getting closer, but I still need a little clarification.|
Please do not take offense, but I think it would help if you answered my questions directly, one by one, with a Yes or No, Right or Wrong, etc., adding more detail if required. Since your requirements appear to be rather complex, I want to make sure I understand your needs before I propose, or even spend time working on, any code.
1 - At this point I have to assume that since you will be varying the Range that will get copied, we can not hard code any Ranges like was done in the sample code you provided. We have to have the code determine which rows to copy based on which rows contain the word Copy in Column H. Is that a correct assumption?
2 - I have noticed that in the examples you have given, the destination row is sometimes 2 rows higher than the source row. (e.g. Sheet1!B16:B35 to Sheet2!B18:B37) but in other cases the destination is 22 rows lower than the source (e.g. Sheet1!B77:B96 to Sheet2!B50:B69) Am I interpreting the examples correctly?
2a - If the answer to 2 is Yes, how would you expect the code to know what row to use as the destination, based on just the word Copy in Column H? Will there be some other key for the code to check?
3 - I see from your examples that Column B gets copied to Column B, C gets copied to F, and D, E, and F get copied to G, H and I respectively. Am I interpreting the examples correctly?
4 - With regards to your Problem 2, where you state that you want these updates to occur automatically, let me explain how that could work so you can tell us how you want it to happen.
Excel has something called a Worksheet_Change event which can monitor a worksheet and "fire" (run) whenever a change occurs within the worksheet. Typically, the first few lines of the code will check and see what changed or where did the change occur or some combination thereof.
If Target.Address = "$H$1" Then... do something
If Target.Column = 4 Then... do something
If Target.Address = "$B$16" And _
Range("H" & Target.Row) = "Copy" _
Then... do something
So, you have to tell us when you want the update to occur.
Do you want the range to be copied as soon as you enter Copy in Column H? Do you want the range to be copied as soon as you enter data in the range and there is also the word Copy in Column H of that row? etc.
If you want the updates to occur automatically without having to run the code manually, you have to tell us what event(s) have to occur for the code to run.