Sorting issue

Hi There,

Underneath sub bugs on:

ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Any ideas?

Best Regards, Sige


Sub MakeNAMS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("7:7").Delete Shift:=xlUp
ws.Range("B7").Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3, 4, 5, 6, _
7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=False,
PageBreaks:=False, _
SummaryBelowData:=True
ActiveWindow.DisplayOutline = True

Next ws
End Sub

Hi Sige,

You need to qualify the sort key range: change


> ws.Range("B6").Sort Key1:=Range("B6"),

to

ws.Range("B6").Sort Key1:=ws.Range("B6"),


---
Regards,
Norman



"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1139492607.652446.304150@o13g2000cwo.googlegroups.com...
> Hi There,
>
> Underneath sub bugs on:
>
> ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>
> Any ideas?
>
> Best Regards, Sige
>
>
> Sub MakeNAMS()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
>
> ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Rows("7:7").Delete Shift:=xlUp
> ws.Range("B7").Subtotal GroupBy:=2, Function:=xlSum,
> TotalList:=Array(3, 4, 5, 6, _
> 7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=False,
> PageBreaks:=False, _
> SummaryBelowData:=True
> ActiveWindow.DisplayOutline = True
>
> Next ws
> End Sub
>

Hi Norman,

I am getting close ... ;o)

One more thing:
How can I show the outlines on all my sheets?

ActiveWindow.DisplayOutline = True shows the outlines only for the
first sheet.

Brgds, Sige

Hi Sige,

Since the DisplayOutline property relates to the window object, I think that
it would be necessary to activate the required sheets, e.g.:

Dim SH As Worksheet
Dim rng As Range

Set rng = Selection

For Each SH In ActiveWorkbook.Worksheets
SH.Activate
ActiveWindow.DisplayOutline = True
Next SH

Application.Goto rng

---
Regards,
Norman



"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1139494345.397141.99050@o13g2000cwo.googlegroups.com...
> Hi Norman,
>
> I am getting close ... ;o)
>
> One more thing:
> How can I show the outlines on all my sheets?
>
> ActiveWindow.DisplayOutline = True shows the outlines only for the
> first sheet.
>
> Brgds, Sige
>

Perfect Norman!

Could you please tell why method "range"of "worksheet" fails here ...?

Sub CheckEmptyRows()
Dim ws As Worksheet
Dim myRow As Long

For Each ws In ActiveWorkbook.Worksheets
myRow = ws.Range("A65536").End(xlUp).Row
ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"
Next ws
End Sub

Thanks, Sige

You have unqualified ranges:

ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"

Those cells refer to the activesheet--not ws.

ws.Range(ws.Cells(7, 16), ws.Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"

or

with ws
.Range(.Cells(7, 16), .Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"
end with

Note the dots in front of .range, .cells.

Sige wrote:
>
> Perfect Norman!
>
> Could you please tell why method "range"of "worksheet" fails here ...?
>
> Sub CheckEmptyRows()
> Dim ws As Worksheet
> Dim myRow As Long
>
> For Each ws In ActiveWorkbook.Worksheets
> myRow = ws.Range("A65536").End(xlUp).Row
> ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
> "=SUM(RC[-13]:RC[-1])=0"
> Next ws
> End Sub
>
> Thanks, Sige

--

Dave Peterson