Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 22nd 05, 10:52 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default 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


  #2   Report Post  
Old November 23rd 05, 12:08 AM posted to microsoft.public.excel.setup
Gord Dibben
 
Posts: n/a
Default 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


  #3   Report Post  
Old November 23rd 05, 03:33 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Workbook vs Worksheet?? Dan B Excel Worksheet Functions 3 November 7th 05 09:02 PM
Inserting Filtered RC cell information into other worksheets Dennis Excel Discussion (Misc queries) 10 July 30th 05 01:54 AM
Search/Match between 2 x separate Worksheets and populate result in third worksheet Alan Bartley Excel Discussion (Misc queries) 1 April 11th 05 05:21 AM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 05:37 AM
copyright and worksheet protection dow Excel Discussion (Misc queries) 2 January 3rd 05 03:07 PM


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017