苦苦挣扎于宏，个人工作簿问题 [英] Struggling with Macros, personal workbook problems

问题描述

我尝试创建一个个人工作簿宏，以便它可以在任何新文档中工作，但每次运行它时都会出现问题。

显然它是引用原始文档在任何新工作簿中创建宏并且无法正确运行的文档。

我强调了问题所在的区域。

Sub ONC_process（）

'

'ONC_process宏

'格式ONC流程


'

'键盘快捷键：Ctrl + m

'

   范围（"A：A，B：B，E：E，G：G"）。选择

   范围（"G1"）。激活

    Selection.Delete Shift：= xlToLeft

   范围（"A：A，B：B，C：C"）。选择

   范围（"C1"）。激活

    Selection.ColumnWidth = 14.43

   列（"C：C"）。选择

   列（"C：C"）。EntireColumn.AutoFit

   行（"22:22"）。选择

    ActiveWindow.SelectedSheets.HPageBreaks.Add之前：= ActiveCell

   范围（"A2：B2，A21，A22，B22，A31"）。选择

   范围（"A31"）。激活

   使用Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65535

        .TintAndShade = 0

        .PatternTintAndShade = 0

   结束与$
   范围（"A2：C21"）。选择

    ActiveWorkbook.Worksheets（" QueueDetail_20161118_103850"）。Sort.SortFields.Clear

    ActiveWorkbook.Worksheets（" QueueDetail_20161118_103850"）。Sort.SortFields.Add _

       键：=范围（"C2：C21"），SortOn：= xlSortOnValues，订单：= xlAscending，_
$
        DataOption：= xlSortNormal

   使用ActiveWorkbook.Worksheets（"QueueDetail_20161118_103850"）。排序

        .SetRange Range（" A2：C21"）

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        。应用

   结束与$
   范围（"A22：C31"）。选择

    ActiveWorkbook.Worksheets（" QueueDetail_20161118_103850"）。Sort.SortFields.Clear

    ActiveWorkbook.Worksheets（" QueueDetail_20161118_103850"）。Sort.SortFields.Add _

       键：=范围（"C22：C31"），SortOn：= xlSortOnValues，订单：= xlAscending，_
$
        DataOption：= xlSortNormal

   使用ActiveWorkbook.Worksheets（"QueueDetail_20161118_103850"）。排序

        .SetRange范围（"A22：C31"）

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        。应用

   结束

    范围（" G36"）。选择

结束子

解决方案

尝试替换所有出现的

ActiveWorkbook.Worksheets（" QueueDetail_20161118_103850"）

with

ActiveSheet

I tried creating a personal workbook macro, so that it would work across any new document, but every time I run it a problem arises.

Apparently it's referencing the original document in which the macro was created and not running correctly in any new workbook.

I've underscored the area which is the problem.

Sub ONC_process()
'
' ONC_process Macro
' Format ONC process
'
' Keyboard Shortcut: Ctrl+m
'
    Range("A:A,B:B,E:E,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A:A,B:B,C:C").Select
    Range("C1").Activate
    Selection.ColumnWidth = 14.43
    Columns("C:C").Select
    Columns("C:C").EntireColumn.AutoFit
    Rows("22:22").Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Range("A2:B2,A21,A22,B22,A31").Select
    Range("A31").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A2:C21").Select
    ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort.SortFields.Add _
        Key:=Range("C2:C21"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort
        .SetRange Range("A2:C21")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A22:C31").Select
    ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort.SortFields.Add _
        Key:=Range("C22:C31"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("QueueDetail_20161118_103850").Sort
        .SetRange Range("A22:C31")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("G36").Select
End Sub

解决方案

Try replacing all occurrences of

ActiveWorkbook.Worksheets("QueueDetail_20161118_103850")

with

ActiveSheet

这篇关于苦苦挣扎于宏，个人工作簿问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！