formula to dynamic single cell on a different spreadsheet, same workbook

[Gilesth]

Hi all,

ok trying to get the value of a cell from another spreadsheet and getting an error.

    Sheets("Sheet1").Select    
    Range("B5").Select
    Selection.End(xlToRight).Select ' could be 10 to 20 columns
    ActiveCell.Offset(1000000, -1).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select ' possible max row 1000
     
    GrandTotal = ActiveCell.Address '~~~~~~~~~ This  gives the cell address as it is dynamic for both its row and column, e.g. cell address "O279"

    Rowid = ActiveCell.Row ' Current Row for Cell
    ColId = ActiveCell.Column ' Current Col for cell
    Row = Rowid - 1 ' Adjustment for formula
    Col = ColId - 1 ' Adjustment for formula
    
    Sheets("Sheet2").Select
    Range("D5").Select

   'Trying to add the formula to bring back the value, but cannot get the following to work, need it to be a formula rather than just the value which I can do.

   ' ActiveCell.Formula = "=SUM('Sheet1'! & GrandTotal &)"
    
    'ActiveCell.FormulaR1C1 = "='Sheet1'!R[" & Row & "]" & "C[" & Col
    
    'ActiveCell.FormulaR1C1 = "=SUM('Sheet1'!R[Row]C[Col])"

"

 

[Joe4]

Can you just explain (in simple English) exactly how you are determining which cell to copy?
Maybe show us a sample of your data?

 

[Gilesth]

It is the bottom right cell from a large table, the number of rows and columns changes each time the file is used. The cell is a grand total of the rows above it.

 

[Joe4]

If there is nothing below and to the right of that last cell in your table, you could simply find it like this:

    Sheets("Sheet1").Select
    Range("A1").SpecialCells(xlLastCell).Select
    GrandTotal = ActiveCell.Address

And then you could continue to use the rest of your Row/Column calculations.

 

[Gilesth]

Thanks Joe, finding the cell was not the issue, but I had issues with using the varible. Sorted now, amended it to the following and it works!

ActiveCell.Formula = "=SUM('Sheet1'!" & GrandTotal & ")" ' Works :)
ActiveCell.Formula = "=SUM('Sheet1'! & GrandTotal &)" ' Did not work, syntax wrong :)

 

[Joe4]

Thanks Joe, finding the cell was not the issue, but I had issues with using the varible. Sorted now, amended it to the following and it works!

ActiveCell.Formula = "=SUM('Sheet1'!" & GrandTotal & ")" ' Works :)
ActiveCell.Formula = "=SUM('Sheet1'! & GrandTotal &)" ' Did not work, syntax wrong :)

OK, you were not quite clear in exactly what your exact question/issue was.

That is correct. The important thing to remember in VBA code is that everything between double-quotes is treated as literal text.
So all variables must be OUTSIDE of your double quotes, and joined with an "&".