Solved macro run from different locations

March 19, 2013 at 11:42:50
Specs: Windows 7
I have created a macro that runs from cell E4. Is it possible to have the macro then run from cell O4, or do I have to create a new macro for cell O4.

Please help


See More: macro run from different locations

Report •


✔ Best Answer
March 22, 2013 at 12:50:59
I like DerbyDad03's direction, but we should go further. Make the function non-range specific, and add a second macro that runs the now generic function on the old range.
Sub AutoSort()
  AutoSort_V3 Range("E3:K32")
End Sub

Public Sub AutoSort_V3(sortRange As Range)
  With sortRange.Parent.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortRange(1), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub

Now, you can make a new function that runs your sort against the new range.

Sub NewSort()
  AutoSort_V3 Range("O3:T32")
End Sub

How To Ask Questions The Smart Way



#1
March 19, 2013 at 12:19:31
Maybe! It depends on:
- What the macro does.
- How the macro does it.
- How you call the macro.

So yeah, maybe.

How To Ask Questions The Smart Way


Report •

#2
March 19, 2013 at 13:21:46
re: "I have created a macro that runs from cell E4"

How does it "run from cell E4"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 20, 2013 at 12:25:56
Hi Razor2.3,
Thnks for replying, sorry couldn't get back to you sooner
The Macro is a recorded Macro of 'Data Sort' across columns E,F,G,H,I,J
through rows 4-32. Row 3 is the Headings title for each column
I have called the Macro 'autosort'

What i require is to 'paste' this macro to 'O4' to then complete the function across
columns O,P,Q,R,S,T, and so on across the worksheet

Hopefully you can see the attached macro

<preSub autosort()
'
' autosort Macro
'

'
Range("E3:K32").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E4:E32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F4:F32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G4:G32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H4:H32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I4:I32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J4:J32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("E3:K32")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Subpre>


Thanking you for your help

Muts


Report •

Related Solutions

#4
March 20, 2013 at 12:27:21
Hi DerbyDad03
Thnks for replying, sorry couldn't get back to you sooner
The Macro is a recorded Macro of 'Data Sort' across columns E,F,G,H,I,J
through rows 4-32. Row 3 is the Headings title for each column
I have called the Macro 'autosort'
What i require is to 'paste' this macro to 'O4' to then complete the function across
columns O,P,Q,R,S,T, and so on across the worksheet

Hopefully you can see the attached macro

<preSub autosort()
'
' autosort Macro
'

'
Range("E3:K32").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E4:E32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F4:F32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G4:G32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H4:H32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I4:I32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J4:J32") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("E3:K32")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Subpre>


Thanking you for your help

Muts


Report •

#5
March 20, 2013 at 13:34:06
First, a posting tip:

You are not using the pre tags correctly. Your brackets are wrong and you didn't use a slash at the end. Click on the pre icon below "Post Reply!" and see what is placed in the text entry field. Once you have the tags in your post, paste your code between the 2 tags, like this (I added a space so the tag shows in this post)

<pre>Put your code between these 2 tags</ pre>

As for your code, you said this:

"The Macro is a recorded Macro of 'Data Sort' across columns E,F,G,H,I,J". However, your code actually sorts E:K via this instruction:

.SetRange Range("E3:K32")

Am I missing something?


BTW...I'm pretty sure that this code does the same thing as your's, but with a lot less instructions:

Sub AutoSort_V2()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E3"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("E3:K32")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

As for your requirement: "What i require is to 'paste' this macro to 'O4' to then complete the function across columns O,P,Q,R,S,T, and so on across the worksheet"

I'm not quite sure what you mean by that. You don't "paste a macro" to a cell.

Are you saying that you want to sort Column O:T, in the same manner as you are sorting E:K?

When you say "and so on across the worksheet" do you mean that you have other ranges, e.g X:AD, AH:AN, etc?

If that's the case, you could simply replicate the code, changing the Key and SetRange values for each Range you want to sort, but that is kind of a brute force method.

A more elegant method would be to loop through that one block of code, updating the range values each time, assuming there is some sort of pattern, e.g. Sort 7 Columns, Skip 3, Sort 7 Columns, Skip 3, etc.

Please clarify your requirements and we'll see waht we can do to help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 20, 2013 at 13:59:18
hi,
the loop solution sounds what i am looking for and your assumptions are correct.
however having run you macro, col E sorts fine, col F has not sorted. I do need to have all columns sort as per the macro i recorded.

E F G H etc
42 21 18 9
33 17 12 5
33 15 12 6


Report •

#7
March 20, 2013 at 17:16:26
Unless you tell me what columns you need sorted, I can't offer any more help.

"...columns O,P,Q,R,S,T, and so on across the worksheet" is not enough information.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 21, 2013 at 10:34:01
'A more elegant method would be to loop through that one block of code, updating the range values each time, assuming there is some sort of pattern, e.g. Sort 7 Columns, Skip 3, Sort 7 Columns, Skip 3, etc'

Your loop idea above would be ideal, from 'E4',
sort 7, skip 3, sort 7, skip 3, etc

If you know about the game of golf, i am trying to establish the scores in order based on the countback criteria, if not, its to flaming long to explain.

I am sure you have realised that i am new to this macro creation, but am very appreciative of the help being given

Cheers
Muts


Report •

#9
March 22, 2013 at 12:50:59
✔ Best Answer
I like DerbyDad03's direction, but we should go further. Make the function non-range specific, and add a second macro that runs the now generic function on the old range.
Sub AutoSort()
  AutoSort_V3 Range("E3:K32")
End Sub

Public Sub AutoSort_V3(sortRange As Range)
  With sortRange.Parent.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortRange(1), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub

Now, you can make a new function that runs your sort against the new range.

Sub NewSort()
  AutoSort_V3 Range("O3:T32")
End Sub

How To Ask Questions The Smart Way


Report •

#10
March 23, 2013 at 06:48:14
Thank you so very much for your help, I have tweaked the answers back from yourself and DerbyDad03 and ended up with the following that does exactly what I require

Sub AutoSort()
 AutoSort_V3 Range("E3:K32")
End Sub
Public Sub AutoSort_V3(sortRange As Range)
 With sortRange.Parent.Sort
 .SortFields.Clear
 .SortFields.Add Key:=sortRange(1), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 .SortFields.Add Key:=sortRange(2), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 .SortFields.Add Key:=sortRange(3), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 .SortFields.Add Key:=sortRange(4), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 .SortFields.Add Key:=sortRange(5), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 .SortFields.Add Key:=sortRange(6), _
 SortOn:=xlSortOnValues, Order:=xlDescending, _
 DataOption:=xlSortTextAsNumbers
 .SetRange sortRange
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
End Sub
Sub NewSort()
 AutoSort_V3 Range("O4:U32")
End Sub
Sub NewSort1()
 AutoSort_V3 Range("Y4:AE32")
End Sub
Sub NewSort2()
 AutoSort_V3 Range("AI4:AO32")
End Sub

edited by moderator: Fixed PRE tags -Razor2.3


Report •


Ask Question