Help: Sort Macro won't work in other worksheets

[pigsknpike]

Hi:

This is my first post to this Message Board so I hope I apologize if I am violating any protocol of this website.

I've been attempting to record a macro that will:
1. Select all data in a spreadsheet with a header row
2. Sort that data based on numerical values in a specific column.
3. Then copy the data in a specific column and paste those values in a new blank column so that the same value appears twice in the same row.
4. The list of data will increase as time goes on...More rows of data will be added but the number of columns will stay the same.

When I record the macro using all absolute references, it works fine as long as I remain in the specific worksheet that recorded the macro in. If I try to run the macro in another worksheet in the same workbook, it seems to run itself again on the original worksheet. When I look at the code it appears that the macro has selected the specific worksheet that it was recorded in and cannot be used in other worksheets without changing the code. I have tried to record using some relative references but I don't seem to be grasping the subtleties of absolute vs. relative as it pertains (if it does) to individual worksheets w/in the same workbook. I have been unable to find a suitable educational video on the web to help me over this hurdle.
Here's what the code looks like:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 251
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 248
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 244
    ActiveWindow.ScrollRow = 243
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 236
    ActiveWindow.ScrollRow = 231
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 217
    ActiveWindow.ScrollRow = 212
    ActiveWindow.ScrollRow = 211
    ActiveWindow.ScrollRow = 206
    ActiveWindow.ScrollRow = 204
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 197
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 2
    ActiveWorkbook.Worksheets("PRACTICE").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PRACTICE").Sort.SortFields.Add Key:=Range("C2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PRACTICE").Sort
        .SetRange Range("A2:AK284")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 257
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 254
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 250
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 248
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 243
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 237
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 226
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    Range("AG2").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("A2").Select
    Application.CutCopyMode = False
    Range("D2").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Any suggestions?
Could I record this differently to avoid this happening?
Do I need to just go into the code an edit it? What changes?

I am just beginning to learn VBA so I don't have much to fall back on. Any help would be appreciated.

Thanks

 

[Norie]

Relative references don't really refer to worksheets.

You'll need to change that code if you want it to run on any worksheet.

That could be as simple as replacing Worksheets("PRACTICE") with ActiveSheet so the code will run on the active sheet.

As for recording the code differently, you can't really do that - what's been recorded is what you've done.

Looks like a lot of scrolling actually.

You can safely remove all of the code that does that, eg the ActiveWindow.ScrollRow etc.

Once that's been done the actual parts of the code you want can be tidied up too.

 

[pigsknpike]

Thanks, Norie