Sort with a Macro

Question

I have a spreadsheet with 8 columns of data. (Row 1 contains my headers).

I am trying to record a macro that I do not have to edit afterwards.

I press record macro, I highlight my data, I go to the data tab and select sort. I then proceed to sort data in decending order by value of column D. I press stop recording macro.

When I play this macro on the same sheet, it works well. When I play this macro back on a different worksheet in the same file that is identical to the orniginal, nothing happens.

How can I record a macro to do what I would like without editing the macros in VBA?

Thanks for your help.

Sub Macro1()
'
' Macro1 Macro
'

'
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D1316" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H1316")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Solution

This will sort the same way for you.

Basically, you use two variables to define the data that you'd like to sort and the key that you're sorting on. Then you perform the sort using the variables. This one is fairly simple, but it should give you some insight on writing your own code.

For instance, if you wanted to sort on two keys, you could use key2, etc.

Sub Joe()

'Define the variables
Dim rngData As Range
Dim rngKey As Range

'Set the variables
Set rngData = Range("A:H")
Set rngKey = Range("D1")

'Perform the Sort
rngData.Sort key1:=rngKey, order1:=xlDescending, Header:=xlYes

End Sub

Comments

[Raystafarian]

You're recording it on a specific sheet, so it's probably sorting that sheet each time you execute the macro. Why don't you want to edit it in VBA?

[Joe]

@Raystafarian Thanks for responding so quickly. I am only a beginner with macros, but it was my understanding that if a macro was recorded on one sheet in an excel file, that you could then run that same macro on another worksheet in the same file. I see in VBA where it is referencing my specific worksheet. Can I record the macro in such a way were it doesn't reference this worksheet? Else, how should I edit the code so that I can apply this macro to any worksheet in the same workbook? Thanks for your help.

[Raystafarian]

Are you afraid of editing the code? It's not a big deal at all. If you feel comfortable learning that, go ahead and edit your question to include the code and we'll help you get it universal.

[Joe]

@Raystafarian Not at all! I am quite excited to gain an understanding of the coding. At this point, I was simply curious why it was working the way that it was. I've updated the post with the code. Thanks for your help.