Sort WorkSheetName unknown

H

Helmut

How can I make the following work on any worksheet and on whatever the new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Add
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
C

carlo

instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use usedrange as
range.

If that doesn't help, try to give some more information.

Carlo
 
H

Helmut

I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
R

Roger Govier

Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
 
H

Helmut

Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

Roger Govier said:
Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

Helmut said:
I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Helmut

It sorted fine on the sample of data I used to test it.
Were you on the correct sheet when you ran the macro?
What happens if you step through the macro with F8? What do you see in the
immediate window for lr and lc?

--

Regards
Roger Govier

Helmut said:
Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

Roger Govier said:
Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns,
xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

Helmut said:
I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

:

instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use usedrange as
range.

If that doesn't help, try to give some more information.

Carlo

How can I make the following work on any worksheet and on whatever
the
new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Add
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Click to expand...
Click to expand...
Click to expand...
 
H

Helmut

Using F8 I see nothing happening on the window at all for lc nor for lr

Roger Govier said:
Hi Helmut

It sorted fine on the sample of data I used to test it.
Were you on the correct sheet when you ran the macro?
What happens if you step through the macro with F8? What do you see in the
immediate window for lr and lc?

--

Regards
Roger Govier

Helmut said:
Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

Roger Govier said:
Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns,
xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

:

instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use usedrange as
range.

If that doesn't help, try to give some more information.

Carlo

How can I make the following work on any worksheet and on whatever
the
new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Add
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Helmut

if you want, you can email the file to me and I will take a look.
to send direct mail to
roger at technology4u dot co dot uk
Do the obvious with at and dot

--

Regards
Roger Govier

Helmut said:
Using F8 I see nothing happening on the window at all for lc nor for lr

Roger Govier said:
Hi Helmut

It sorted fine on the sample of data I used to test it.
Were you on the correct sheet when you ran the macro?
What happens if you step through the macro with F8? What do you see in
the
immediate window for lr and lc?

--

Regards
Roger Govier

Helmut said:
Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

:

Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns,
xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

:

instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use usedrange
as
range.

If that doesn't help, try to give some more information.

Carlo

How can I make the following work on any worksheet and on
whatever
the
new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Add
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Click to expand...
Click to expand...
Click to expand...
 
C

carlo

it looks like your usedrange hasn't been recognised (as it is still in
small letters)
try:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=activesheet.usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

hth Carlo
 
R

Roger Govier

File received, sorted and reply sent by email

The problem was entirely mine.
With Helmut selecting Range("A11"), I had also in advertently typed A11
instead of A1 into the calculation of lastrow and lastcolumn

It should of course had read
lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

--

Regards
Roger Govier

Roger Govier said:
Hi Helmut

if you want, you can email the file to me and I will take a look.
to send direct mail to
roger at technology4u dot co dot uk
Do the obvious with at and dot

--

Regards
Roger Govier

Helmut said:
Using F8 I see nothing happening on the window at all for lc nor for lr

Roger Govier said:
Hi Helmut

It sorted fine on the sample of data I used to test it.
Were you on the correct sheet when you ran the macro?
What happens if you step through the macro with F8? What do you see in
the
immediate window for lr and lc?

--

Regards
Roger Govier

Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

:

Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns,
xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

:

instead of activeworkbook.worksheets(bla) you could use
activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use
usedrange as
range.

If that doesn't help, try to give some more information.

Carlo

On Jan 9, 4:41 pm, Helmut <[email protected]>
wrote:
How can I make the following work on any worksheet and on
whatever
the
new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.Add
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify Sort Routine to inlcude All Data 2
Undo Macro Action 3
Sorting Question 5
VBA 2 Codes 2
Clear Check Box 2
.Sort.SortFields 1
Sort by one column then another. 2
Pictures not being sorted in VBA 2

Top