Can't copy value from the Last Row to other worksheet-CodePudding

I have situation such as this:

 Sub AutoSum()
     Dim Sumcalc As Double
     Dim LR As Long
     Dim Sm As Range

     Range("E" & Cells(Rows.Count, "E").End(xlUp).Row   1).Value = _
      WorksheetFunction.Sum(Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row))
    
    LR = Range("E:E").SpecialCells(xlCellTypeLastCell).Row
    Range("E" & LR).Select
    Selection.Copy
    ThisWorkbook.Worksheets("Test").Range("I1").PasteSpecial xlPasteValues

and I cannot copy the value from the last row to the other worksheet. It returns a blank cell. How can I copy the value from the last row to the cell located in other sheet?

CodePudding user response：

xlCellTypeLastCell not necessarily gives the last cell with data (can for example be the last row that has some formatting). You use another technique two lines before using End - why not write this into a variable and reuse it?

Have a look to the following snippet.

With ActiveSheet
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "E").End(xlUp).row
    .Range("E" & lastRow   1).Value = WorksheetFunction.Sum(Range("E2:E" & lastRow   1))
    
    ThisWorkbook.Worksheets("Test").Range("I1").Value = .Range("E" & lastRow   1).Value
End With