将sum放在行结束后并应用于所有工作表

Question

我有一个带有多个不同名称的工作表的工作簿。我想在最后一行列后面加一个和;B，我想把它应用到工作簿中的所有工作表。此外，我还需要将总和以粗体显示，并用黄色突出显示。

请帮助我适当的VBA宏

编辑:

我在这里也看到了这段代码，但这只会在一个预定义的工作表名称为Sheet1的工作表上放置一个总和。我的床单名字不一样。我需要一个宏，它可以将此功能应用于所有工作表，而不管工作表的名称。

Sub test() 
Dim ws as worksheet 
Dim VR as Long 
Dim rng as Range
Set ws = Thisworkbook.Worksheets("Sheet1")  
With ws
VR = .Cells(.Rows.Count, 8).End(xlUp).Row  
End with
Set rng = ws.Cells((VR+1), 10)  
rng.Offset(2,0).Formula ="=SUM(J2:J" & VR & ")"
End Sub

Answer 1

我建议使用这里所描述的FIND()函数

此外，您似乎正在积极地针对第10列，以使您的总和低于最后一个值。列数将来可能会改变。为了使其更健壮，您可以检查列名，或者始终将总和放在范围的最后一列下面。我还建议使用subtotal而不是SUM函数109，同样，只是为了使它更健壮。

Answer 2

你只需要循环遍历你所有的工作表

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets  ' loop through all worksheets
' find the last used row in column A in each worksheet
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' one below last row write the sum formulas
ws.Cells(LastRow + 1, "A").Formula = "=SUM(A2:A" & LastRow & ")"
ws.Cells(LastRow + 1, "B").Formula = "=SUM(B2:B" & LastRow & ")"
Next ws

Answer 3

添加合计数据

Sub AddTotals()

Const FirstRowAddress As String = "A2:B2"
Const EmptyRowsBetween As Long = 0

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

Dim ws As Worksheet
Dim lCell As Range
Dim rCount As Long
Dim sAddress As String

For Each ws In wb.Worksheets
With ws.Range(FirstRowAddress)
Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
.Find("*", , xlFormulas, , , xlPrevious)
If Not lCell Is Nothing Then
rCount = lCell.Row - .Row + 1
sAddress = .Resize(rCount).Columns(1).Address(, 0)
With .Offset(rCount + EmptyRowsBetween)
.Formula = "=SUM(" & sAddress & ")"
' To get rid of the formulas use:
'.Value = .Value
End With
Else
Debug.Print "No relevant data in worksheet '" & ws.Name & "'."
End If
End With
Next ws

MsgBox "Totals added.", vbInformation

End Sub