Sheetoffset - alternatives ?

Each time one formula is pasted, all the previous sheetoffset formulas recalculate. This can be expensive when a lot of formulas are involved.

One method to speed things up is to set sheet calculations to manual, paste your formulas, then reset the sheet calculation back to automatic. This will prevent unnecessary recalculations.

Please Login or Register  to view this content.

Alternatively, if you only want the values in the cells and not the formulas, you could paste the values directly from your macro. That would be much faster and easier.

Please Login or Register  to view this content.

Originally Posted by Lukael

the only "bad" thing is that I have 79 inflicting rows

I don't understand what that means

The second option, which is exactly what I was searching for,...then these cell values must be filled from sheet No.3 till last sheet.... So, first problem is that I don't know how to assign that code to perform from sheet 3 to 13, within my macro. And second problem (which I think is bigger) is that It only copies values.

You can have the code copy links (without the sheetoffset function) instead of just values. Links would be much more efficient than using the sheetoffset function. I don't follow your data configuration; what you want linked.

I don't understand what that means

This means that my code for your 1st option (suggestion) looks like this, example for first few rows (79 rows in total) :

Please Login or Register  to view this content.

As you can see, this code is very long, If written for all rows..

You can have the code copy links (without the sheetoffset function) instead of just values. Links would be much more efficient than using the sheetoffset function. I don't follow your data configuration; what you want linked.

And I don't understand what this means

Originally Posted by Lukael

This means that my code for your 1st option (suggestion) looks like this, example for first few rows (79 rows in total) :


And I don't understand what this means

Please Login or Register  to view this content.

Originally Posted by Lukael

Code works, just a little bit explanation of the code please :

Please Login or Register  to view this content.

1. What exactly does .Adress(External:=True) do ?

2. Why or will code never be buggy, since you used 2 FOR loops with same variable (k) ?

1.) Its returns the full text address (sheet name and cell address) of the range e.g.; January!A5 . Highlight the Address keyword in the code and hit F1 for help on that range property.

2.) Never buggy. They are separate loops. The variable k can be reused.