Autosum Range and posting results in another sheet on the same workbook

[DiogoCuba]

Hello, Masters!

I need your help to get a code for a report I receive every month.

In this report on column CU I have all costs of a certain product and I need to sum them up from cell CU8 until the last row with data; Then I need to get the result of that sum to another sheet named "Hotel Costs" on cell B2.

I have came up to this so far, but it only sum the CU8 to the last row, but I cannot figure out how to copy that to the other sheet:

Sheets("Hotel").Activate
Range("CU" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R8C:R[-2]C)"
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Please keep in mind that the report will never have a fixed last row.

If there is another way to do that I am happy to use it as well!

Thanks!

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomCU As Long
    bottomCU = Range("CU" & Rows.Count).End(xlUp).Row
    Range("CU" & bottomCU + 1).Formula = "=sum(CU8:CU" & bottomCU & ")"
    Range("CU" & bottomCU + 1).Copy
    Sheets("Hotel Costs").Range("B2").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[DiogoCuba]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomCU As Long
    bottomCU = Range("CU" & Rows.Count).End(xlUp).Row
    Range("CU" & bottomCU + 1).Formula = "=sum(CU8:CU" & bottomCU & ")"
    Range("CU" & bottomCU + 1).Copy
    Sheets("Hotel Costs").Range("B2").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thanks mumps, but I have just tried and it returned 0 on the spreadsheet. Any ideas why?
In addition, I have review the format of the cells on the range CU and they are all formatted as numbers.

 

[DiogoCuba]

Thanks mumps, but I have just tried and it returned 0 on the spreadsheet. Any ideas why?
In addition, I have review the format of the cells on the range CU and they are all formatted as numbers.

I think I realized that I was missing to active the worksheet where the code should run! It's running perfect now!

Thank you very much for your time and work!

 

[Peter_SSs]

I think I realized that I was missing to active the worksheet where the code should run!

For this problem you shouldn't need to do that, though it isn't really a problem if you want to.

I don't know what that sheet name is, so I've used the name "Data" in my alternative code below.
If you want to give it a try in a copy of your workbook, just edit "Data" to the name of the sheet where you are trying to sum column CU.

Sub Total_CU()
  With Sheets("Data").Range("CU" & Rows.Count).End(xlUp).Offset(1)
    .FormulaR1C1 = "=SUM(R8C:R[-1]C)"
    Sheets("Hotel Costs").Range("B2").Value = .Value
  End With
End Sub

 

[DiogoCuba]

For this problem you shouldn't need to do that, though it isn't really a problem if you want to.

I don't know what that sheet name is, so I've used the name "Data" in my alternative code below.
If you want to give it a try in a copy of your workbook, just edit "Data" to the name of the sheet where you are trying to sum column CU.

Sub Total_CU()
  With Sheets("Data").Range("CU" & Rows.Count).End(xlUp).Offset(1)
    .FormulaR1C1 = "=SUM(R8C:R[-1]C)"
    Sheets("Hotel Costs").Range("B2").Value = .Value
  End With
End Sub

Peter_SSs the VB you offered is simpler then the one mumps wrote and also works great!
Thanks for your time and work!

 

[Peter_SSs]

Thanks for your time and work!

No problem, glad to contribute. Thanks for letting us know how the suggestions went.