[已解决]请教在VBS中如何自动读取当前工作表名称（出错：下标越界）

lianke

 出错部分代码：

   ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("K6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("A3:IS23")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

只要工作表名称不是sheet3一使用宏，就会提示下标越界。但是只要将工作表名称修改为sheet3就能正常使用，或者将代码sheet3修改为现在工作表名称也可以正常使用，但是这样的话每次插入新的工作表都会需要修改一下，很是不便。请问要怎么修改才能适用每个不同名称的工作表呢~~！谢谢

全部代码：

Sub Macro2()
'
' Macro2 Macro
'
' 快捷键: Ctrl+e
'
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("K6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("A3:IS23")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("BY:BY").Select
    Selection.Delete Shift:=xlToLeft
    Columns("CW:CW").Select
    Selection.Delete Shift:=xlToLeft
    Columns("FF:FF").Select
    Selection.Delete Shift:=xlToLeft
    Columns("FY:FY").Select
    Selection.Delete Shift:=xlToLeft
    Columns("GN:GN").Select
    Selection.Delete Shift:=xlToLeft
    Columns("GY:GY").Select
    Selection.Delete Shift:=xlToLeft
    Columns("HQ:HQ").Select
    Selection.Delete Shift:=xlToLeft
    Columns("HZ:HZ").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1:IJ1").Select
    Range("IJ1").Activate
    Selection.ClearContents
    ActiveWindow.ScrollColumn = 235
    ActiveWindow.ScrollColumn = 233
    ActiveWindow.ScrollColumn = 232
    ActiveWindow.ScrollColumn = 225
    ActiveWindow.ScrollColumn = 221
    ActiveWindow.ScrollColumn = 215
    ActiveWindow.ScrollColumn = 208
    ActiveWindow.ScrollColumn = 201
    ActiveWindow.ScrollColumn = 193
    ActiveWindow.ScrollColumn = 180
    ActiveWindow.ScrollColumn = 159
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 115
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 78
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 64
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 56
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1:IJ23").Select
    Range("C3").Activate
    Selection.Copy
    ActiveWindow.SmallScroll Down:=30
    Range("A50").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Rows("1:49").Select
    Range("A49").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.Paste
    Range("A5").Select
    Selection.Cut
    Range("B2").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.ColumnWidth = 15
    Range("A1").Select
    Selection.NumberFormatLocal = "000000"
    ActiveCell.FormulaR1C1 = "1"
    Range("A1:A2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").Select
    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("5:247").Select
    Selection.NumberFormatLocal = "0.00_);[红色](0.00)"
    ActiveWindow.SmallScroll Down:=0
    Range("B16").Select
    ActiveWindow.SmallScroll Down:=-18
    Range("A4").Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 1
        .SplitRow = 0
    End With
    ActiveWindow.FreezePanes = True
    Range("B7").Select
    ActiveWindow.SmallScroll Down:=-39
End Sub

最佳答案

月排行榜 / 总排行榜

fjmxwrs

2010-7-29 00:28

读取当前工作表名只一句代码就行，为什么要一串这么多？

MsgBox ActiveSheet.Name

ActiveSheet.Name就是当前工作表名

跳转到最佳答案楼层

fjmxwrs

读取当前工作表名只一句代码就行，为什么要一串这么多？

MsgBox ActiveSheet.Name

ActiveSheet.Name就是当前工作表名

fjmxwrs

你指定了sheet3工作表，没有找到此表，自然就下标越界了。

ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("K6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort

改成

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("K6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort

或者改成

增加个变量

Dim I As String

I=ActiveSheet.Name

ActiveWorkbook.Worksheets(I).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(I).Sort.SortFields.Add Key:=Range("K6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(I).Sort

[此贴子已经被作者于2010-7-29 0:34:45编辑过]

lianke

非常感谢~~！ 问题解决了~~！  在请教一个问题    如何将工作表名称重命名为表格（例如：A1）里的文字或数字，也就是说表格（例如：A1）里的文字或数字有所改变而工作表名称也会随之自动修改为相同的文字或数字    再次感谢~~！

fjmxwrs

ActiveSheet.Name = [A1]

dong66094799

路过,学到东东,嘿嘿.