dynamic cell reference from other sheets

Hi everyone. New day new challange.

I am looking for a way using Excel VBA wherein I Need to copy cell values from column M from (Sheet3,Sheet4,Sheet5,Sheet6,Sheet7) to use them in a formula in Sheet 1. Now this is simple but the trick part is, the column M in all those Sheets is dynamic and number of rows Keep changing everytime a new Report is generated by the user. The formula column in sheets 1 that Needs to be generated using column M values from other Sheets is also dynamic and the number of rows in this column dynamically changes corresponding to the number of rows in column M .

Thanks

Not sure of what you're after, does this help?

Please Login or Register  to view this content.

Looks good. I tried it but some Problems are there. One of the main issues is that whenever we talk about a dynamic row with changing number of rows, we use xlup function. What if there is another set of calculations beneath that table and the last row of the column i am interested in is not the last row of the sheet but the last low i am interested in is where the Input range Ends.

for example my in put is 10 and 14 (on sheet 1). So i have the column (on other Sheets) with values 10 11 12 13 14. But few rows later there starts another set of calculations. How do I reference this dynamic column (10 to 14) in my formulas?

Hy there. I guess I might be able to solve it with few changes but thanks for the main concept. I was having difficulty giving the Syntax for counting the number of rows.

I have another smal question, all the values generated by the macro are upto 7 decimals places . I want the values to be calculated only upto 2 decimal places since thats the accuray i am looking for and the macro is already running too slow.

I am trying with the following and its working but i have to add this code before everyformula which is annoying.

Selection.NumberFormat = "0.00"

Is there a way to give a General code at the start of the macro so that all the values it calculate are upto two decimal places.

Thanks

See my edit too Post #7. Hope that works.

It did Thanks alot

You're welcome. Glad to help out and thanks for the feedback and the reps. Please mark this thread as SOLVED.