macro to sort using relative references

Question

hello, I need to create a relative reference macro to sort a worksheet. I record on Sheet1 and test on Sheet2. All steps of the macro work well but on the sorting step it keeps going back to S heet1. I checked the VBA code which shows Sheet1 address. Any ideas?

Answer 1

Historically, "relative recording" has only referred to how it deals with range references.

So, if you select the cell 2 rows down from the active cell in relative mode it will record

ActiveCell.Offset(2,0).Select and in absolute mode it will record e.g. Range("D4").Select

Whether recording relative or absolute mostly the code works on the ActiveSheet.

I would regard it as strange that the code for the Sort method includes a specific sheet reference - whether in relative or absolute mode.

It is arguably a bug in the macro recorded code; if you feel it should be corrected you could post the suggestion on http://excel.uservoice.com  .  If I had any votes left I would support it!

Meanwhile, just replace all occurrences of ActiveWorkbook.Worksheets("Sheet1").Sort with ActiveSheet.Sort

Answer 2

Many thanks Bill. Much appreciated. i will take your suggestion and report the bug.

Answer 3

Hello Bill,

Many thanks for your help. I am pasting the VBA code so that you can advise me better.

Also, and more importantly I need to know WHY it records the sheet name even though I am recording it as a relative macro. Appreciate your advise.

==============================

Sub RelativeMacro()

'

' RelativeMacro Macro

'

'

    ActiveCell.Offset(2, 0).Range("A1:G1").Select

    Selection.Font.Bold = True

    ActiveCell.Range("A1:G31").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell.Range _

        ("A1:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell. _

        Offset(0, 4).Range("A1:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, _

        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort

        .SetRange ActiveCell.Offset(-1, 0).Range("A1:G31")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range("A1").Select

End Sub

Answer 4

I suggest you replace any instances of

  Worksheets("Sheet1")

or

  Sheets("Sheet1")

by

  ActiveSheet

It should then work on any worksheet of a similar format when that sheet is active.

If this doesn't answer your question, please post the VBA code so that we can advise better.