Not switching to next worksheet
 

I am running this macro that sums the range and place the total at the end of
the range. The macro will work on the current worksheet, but will not
automatically calculate the sum on the other worksheet. Is there something
wrong with the coding? Thanks!

Sub AddTotal()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng2 = Range("K8").End(xlDown)
Set rng = Range("k8", rng2)
rng2.Offset(2, 0).Formula = "=SUM(" & rng.Address & ")"
rng2.Offset(2, 0).Borders(xlDiagonalDown).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlDiagonalUp).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeLeft).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeRight).LineStyle = xlNone
With rng2.Offset(2, 0).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng2.Offset(2, 0).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next wks
End Sub

Not switching to next worksheet
 

G

You need to qualify the rng2 and rng Ranges for each worksheet

See amended which does what you want.

Sub AddTotal()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng2 = wks.Range("K8").End(xlDown) 'add wks
Set rng = wks.Range("K8", rng2) 'add wks
rng2.Offset(2, 0).Formula = "=SUM(" & rng.Address & ")"
rng2.Offset(2, 0).Borders(xlDiagonalDown).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlDiagonalUp).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeLeft).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeRight).LineStyle = xlNone
With rng2.Offset(2, 0).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng2.Offset(2, 0).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next wks
End Sub


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 14:52:01 -0800, "G" wrote:

I am running this macro that sums the range and place the total at the end of
the range. The macro will work on the current worksheet, but will not
automatically calculate the sum on the other worksheet. Is there something
wrong with the coding? Thanks!

Sub AddTotal()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng2 = Range("K8").End(xlDown)
Set rng = Range("k8", rng2)
rng2.Offset(2, 0).Formula = "=SUM(" & rng.Address & ")"
rng2.Offset(2, 0).Borders(xlDiagonalDown).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlDiagonalUp).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeLeft).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeRight).LineStyle = xlNone
With rng2.Offset(2, 0).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng2.Offset(2, 0).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next wks
End Sub

Not switching to next worksheet
 

Thanks again!! I'm learning from the little mistakes!

"Gord Dibben" wrote:

G

You need to qualify the rng2 and rng Ranges for each worksheet

See amended which does what you want.

Sub AddTotal()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng2 = wks.Range("K8").End(xlDown) 'add wks
Set rng = wks.Range("K8", rng2) 'add wks
rng2.Offset(2, 0).Formula = "=SUM(" & rng.Address & ")"
rng2.Offset(2, 0).Borders(xlDiagonalDown).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlDiagonalUp).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeLeft).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeRight).LineStyle = xlNone
With rng2.Offset(2, 0).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng2.Offset(2, 0).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next wks
End Sub


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 14:52:01 -0800, "G" wrote:

I am running this macro that sums the range and place the total at the end of
the range. The macro will work on the current worksheet, but will not
automatically calculate the sum on the other worksheet. Is there something
wrong with the coding? Thanks!

Sub AddTotal()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng2 = Range("K8").End(xlDown)
Set rng = Range("k8", rng2)
rng2.Offset(2, 0).Formula = "=SUM(" & rng.Address & ")"
rng2.Offset(2, 0).Borders(xlDiagonalDown).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlDiagonalUp).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeLeft).LineStyle = xlNone
rng2.Offset(2, 0).Borders(xlEdgeRight).LineStyle = xlNone
With rng2.Offset(2, 0).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng2.Offset(2, 0).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next wks
End Sub