VBA 将结果全部显示在第一张纸上，而不是相关的纸上？

Question

我非常接近完成我打算做的事情。我基本上只需要在我的工作簿的所有工作表中的所有列的总和直接在列的末尾。我的问题是答案都出现在第一张纸上，而不是相关的纸上。我不知道我需要改变什么!

这是我正在使用的

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim Lastrow As Long, Lastcol As Long

For Each ws In ThisWorkbook.Worksheets

    Lastrow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Lastcol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    Range(Cells(Lastrow, "D"), Cells(Lastrow, Lastcol)).FormulaR1C1 = "=SUM(R1C:R" & Lastrow - 1 & "C)"

Next ws

End Sub

Answer 1

尝试这个:

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim Lastrow As Long, Lastcol As Long

For Each ws In ThisWorkbook.Worksheets

    Lastrow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Lastcol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    ws.Activate

    Range(Cells(Lastrow, "D"), Cells(Lastrow, Lastcol)).FormulaR1C1 = "=SUM(R1C:R" & Lastrow - 1 & "C)"

Next ws

End Sub

或(完成@John Coleman 的回答)

Sub test()
    Dim ws As Worksheet
    Dim Lastrow As Long, Lastcol As Long

    For Each ws In ThisWorkbook.Sheets

        Lastrow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Lastcol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        ws.Range(ws.Cells(Lastrow, "A"), ws.Cells(Lastrow, Lastcol)).FormulaR1C1 = "=SUM(R1C:R" & Lastrow - 1 & "C)"

    Next ws
End Sub