VBA | Adding the last cell from 2 different sheets

[DevAlex]

Hello I am quite new to VBA and was needing to know how to select the last cell in 2 sheets and sum them together onto a 3rd sheet. The data from the first 2 sheets will vary every week and therefore the code must account for the different lengths in rows. Any help or guidance would be appreciated. Thank you.

 

[Joe4]

Welcome to the Board!

We need a bit more detail than what you have provided.

What columns are these values found in?
Where on this third sheet are you placing this sum (is it the exact same cell every time)?
Do you just want the hard-coded value of the sum, or a formula in the to return the sum of those two values?
Does it have to be VBA (you may be able to do this without VBA - there are Excel formulas that can return the last value found in a column)?

 

[DevAlex]

Welcome to the Board!

We need a bit more detail than what you have provided.

What columns are these values found in?
Where on this third sheet are you placing this sum (is it the exact same cell every time)?
Do you just want the hard-coded value of the sum, or a formula in the to return the sum of those two values?
Does it have to be VBA (you may be able to do this without VBA - there are Excel formulas that can return the last value found in a column)?

Hello,

I have 3 sheets: "Overview", "Sheet1", "Sheet2". I am trying to sum the last cell from "Sheet1" which is in column "Revenue" and row "Total" with the last cell from "Sheet2" which also has the the same column and row names (Revenue and Total). I am trying to place this sum into the "Overview" sheet into the cell "B8". "Sheet1" and "Sheet2" have a different amount of columns and rows but share the "Revenue" column and "Total" Row.

The data in "Sheet1" and "Sheet2" will be updated weekly, which could also include adding additional rows and columns so the VBA code or macro will need to grab the last cell in each of the sheets since the last row and column (Revenue and Total) will stay the same.

All I need is the value of the sum no matter what additional data is added to Sheet1 and Sheet2. VBA is preferred but if there is an easier solution that would be appreciated just as much.

Thank you, I hope that made things clearer.

 

[Joe4]

Is the "Revenue" column always in the same column in Excel (like column "B" or whatever)?
If so, what Excel column is it in?

Is the "Total" row always the very last row with data on the sheet (I don't care that it can change, just that it is the last row on the sheet)?

 

[DevAlex]

Is the "Revenue" column always in the same column in Excel (like column "B" or whatever)?
If so, what Excel column is it in?

Is the "Total" row always the very last row with data on the sheet (I don't care that it can change, just that it is the last row on the sheet)?

The "Revenue" column is always column "AL" in Sheet1 and column "AF" in Sheet2, while "Total" will always be the last row no matter the amount of rows.

 

[Joe4]

This formula should work for you then:

=LOOKUP(2,1/(Sheet1!AL:AL<>""),Sheet1!AL:AL) + LOOKUP(2,1/(Sheet2!AF:AF<>""),Sheet2!AF:AF)

As long as this formula is always going in cell B8 on the "Overview" sheet, there really isn't any need for VBA.

 

[DevAlex]

This formula should work for you then:

=LOOKUP(2,1/(Sheet1!AL:AL<>""),Sheet1!AL:AL) + LOOKUP(2,1/(Sheet2!AF:AF<>""),Sheet2!AF:AF)

As long as this formula is always going in cell B8 on the "Overview" sheet, there really isn't any need for VBA.

That seems to have done the trick, thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!