excel - How to use active sheet reference instead of specific sheet name - Stack Overflow

admin管理员组

文章数量:1123502

I created the following macro extract using record button. Unfortunately it contains reference to "Sheet1". I want it to work in active sheet, regardles of its name.

Range("A1:Q1").Select
    
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
  
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "O1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

How could I change it?

I created the following macro extract using record button. Unfortunately it contains reference to "Sheet1". I want it to work in active sheet, regardles of its name.

Range("A1:Q1").Select
    
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
  
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "O1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

How could I change it?

• excel
• vba

Share Improve this question Follow edited 17 hours ago jonrsharpe 122k3030 gold badges264264 silver badges472472 bronze badges asked 17 hours ago Ilya AleksandrovIlya Aleksandrov 1 New contributor Ilya Aleksandrov is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 2

• 3 Change ActiveWorkbook.Worksheets("Sheet1") to ActiveSheet. – Darren Bartrup-Cook Commented 17 hours ago
• Or use Range("A1:Q1").Worksheet since you already referenced "A1:Q1". – rotabor Commented 9 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -2

You can simply dim a variable as worksheet and then give it the reference to the active worksheet in your workbook. You can do something like this:

Dim wsActive As Worksheet
Set wsActive = ThisWorkbook.ActiveSheet

'You can even test if it worked by using:

MsgBox = "Active table:" & wsActive.Name

Note, however, that using this to making changes in large workbooks might cause you trouble if the wrong worksheet is active when your script runs. Remember always forcing your script to activating the worksheet you need to work in. You can do this whenever you need:

wsWanted.Visible = True
wsWanted.Activate

本文标签： excelHow to use active sheet reference instead of specific sheet nameStack Overflow