时间戳

首页 > 解决方案 > 宏上的命令按钮的作用不同于从列表中选择宏

excel - 宏上的命令按钮的作用不同于从列表中选择宏

问题描述

我不明白为什么会这样，但我有一个宏，可以根据国家名称将数据从一个工作表移动到其他各种工作表。如果我从命令功能区（开发人员选项卡/宏/从列表中选择）中选择宏，则宏可以完美运行。如果我使用分配了宏的命令按钮，或者使用调用宏的 button_click 方法，则结果只是部分的。宏没有完成这项工作，但代码一直运行并且没有错误。我什至可以从列表中选择 button_click 方法，它工作正常。为什么按钮与从宏列表中选择会有不同的行为？

Sub MoveButton_Click()

Call MoveDataToWorksheet

End Sub

Sub MoveDataToWorksheet()

Dim i As Variant
Dim pname As String
Dim rng As Range
Dim lastrow As Long
Dim wslastrow As Long
Dim ws As Worksheet
Dim count As Long
Dim rawdata As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Raw_Data" Or ws.Name = "Charts" Or _
    ws.Name = "Tables" Then
    'skips the sheets I want to keep
    Else
        wslastrow = ws.Cells(Rows.count, "a").End(xlUp).Row
        If wslastrow >= 5 Then
            ws.Range("a5:r" & wslastrow).Delete
        Else
            ws.Range("a5:r" & 6).Delete
        End If
    End If
Next ws

Set rawdata = ThisWorkbook.Worksheets("Raw_Data")
lastrow = rawdata.Cells(Rows.count, "a").End(xlUp).Row
Set rng = rawdata.Range("a5:a" & lastrow)

For Each i In rng
    pname = Cells(i.Row, "a").Value

    For Each ws In ThisWorkbook.Worksheets
        If pname = ws.Name Then
            wslastrow = ws.Cells(Rows.count, "a").End(xlUp).Row + 1
            i.EntireRow.Copy
            If wslastrow >= 5 Then
                ws.Cells(wslastrow, "a").PasteSpecial
            Else
                ws.Cells(5, "a").PasteSpecial
            End If
        End If
    Next ws

        If pname = "South Carolina" Then
            i.EntireRow.Copy
            wslastrow = ThisWorkbook.Worksheets("SC").Cells(Rows.count, "a").End(xlUp).Row + 1
            If wslastrow >= 5 Then
                ThisWorkbook.Worksheets("SC").Cells(wslastrow, "a").PasteSpecial
            Else
                ThisWorkbook.Worksheets("SC").Cells(5, "a").PasteSpecial
            End If
        End If

        If pname = "Saudi Arabia" Then
            i.EntireRow.Copy
            wslastrow = ThisWorkbook.Worksheets("KSA").Cells(Rows.count, "a").End(xlUp).Row + 1
            If wslastrow >= 5 Then
                ThisWorkbook.Worksheets("KSA").Cells(wslastrow, "a").PasteSpecial
            Else
                ThisWorkbook.Worksheets("KSA").Cells(5, "a").PasteSpecial
            End If
        End If

        If pname = "United Arab Emirates" Then
            i.EntireRow.Copy
            wslastrow = ThisWorkbook.Worksheets("UAE").Cells(Rows.count, "a").End(xlUp).Row + 1
            If wslastrow >= 5 Then
                ThisWorkbook.Worksheets("UAE").Cells(wslastrow, "a").PasteSpecial
            Else
                ThisWorkbook.Worksheets("UAE").Cells(5, "a").PasteSpecial
            End If
        End If
Next i

Call FixWSFormulas

End Sub

标签： excelvbacommandbutton

解决方案

要结束这个问题：

有一个隐含ActiveSheet的 in pname = Cells(i.Row, "a").Value，这意味着这可能针对不同的工作表运行，具体取决于您是从宏列表还是命令按钮运行它。

更改为pname = rawdata.Cells(i.Row, "a").Value。

推荐阅读