Plz help in modifying a vba related to Sheets dynamic

[love_guy_1977]

Hi guys,
I’m having a file with sheet named “Data” but this “data” sheet will change its name between time and time. My bellow macro is based on that sheet i.e “Data”. The sheet name in VBA object is Sheet5 (Data).
The first line in the bellow vba is ok but the 4th & 5th & 7th still having the reference of (Data) which Worksheets("Data").
So how can I change Worksheets("Data") to be sheet5. So if any time wanna change the sheet’s name it will not affect the vba.

Sub Sort_Data()
    Sheet5.Select     ‘ Mean sheet(“Data”).Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B45000") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A1:C45000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thank you

 

[doofusboy]

Have you tried something like this?

Sub Sort_Data()
    Sheet5.Select     ‘ Mean sheet(“Data”).Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    [B][COLOR=#ff0000]Sheet5.[/COLOR][/B]SortFields.Clear
    [B][COLOR=#ff0000]Sheet5.[/COLOR][/B]Sort.SortFields.Add Key:=Range("B2:B45000") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With [B][COLOR=red]Sheet5.[/COLOR][/B]Sort
        .SetRange Range("A1:C45000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[love_guy_1977]

IT IS WORKING

Thank you

Have you tried something like this?

Sub Sort_Data()
    Sheet5.Select     ‘ Mean sheet(“Data”).Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    [B][COLOR=#ff0000]Sheet5.[/COLOR][/B]SortFields.Clear
    [B][COLOR=#ff0000]Sheet5.[/COLOR][/B]Sort.SortFields.Add Key:=Range("B2:B45000") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With [B][COLOR=red]Sheet5.[/COLOR][/B]Sort
        .SetRange Range("A1:C45000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub