vba - 如何将 2 个宏组合成 1 个

Question

有人可以建议如何将以下 2 个宏组合成 1 个吗？

Option Explicit

Sub ArchiveReminder()

    Dim rngToCopyFrom As Range

    With Worksheets("MailMerge-Reminder").Columns("A:Q")
        Set rngToCopyFrom = .Resize(LastColumnsRow(.Cells) - 1).Offset(1)
    End With

    PasteRangeValuesToWorksheet rngToCopyFrom, Worksheets("Archive-Reminder").Columns("A:Q") '<~~ paste values to 1st worksheet
    PasteRangeValuesToWorksheet rngToCopyFrom, Worksheets("AcctsDueToBeSusp").Columns("E:U") '<~~ paste values to 2nd worksheet

End Sub


Sub PasteRangeValuesToWorksheet(rngToCopyValuesFrom As Range, rngToPasteTo As Range)
'pastes values from the range passed as the first parameter to the range passed as the second parameter
    Dim lastRow As Long
    With rngToPasteTo
        lastRow = LastColumnsRow(.Cells) '<~~ get last non empty row between all columns of the range to paste to
        .Resize(rngToCopyValuesFrom.Rows.Count, rngToCopyValuesFrom.Columns.Count).Offset(IIf(lastRow = 1, 0, lastRow)).Value = rngToCopyValuesFrom.Value '<~~ paste values
    End With
End Sub


Function LastColumnsRow(rng As Range) As Long
'gets last non empty row between all columns of the passed range
    Dim maxRow As Long, lastRow As Long
    Dim cell As Range
    With rng
        For Each cell In .Resize(1)
            lastRow = .Parent.Cells(.Parent.Rows.Count, cell.Column).End(xlUp).Row
            If lastRow > maxRow Then maxRow = lastRow
        Next cell
    End With
    LastColumnsRow = maxRow

End Function

第一个宏(上)是将信息从工作表 1 复制到工作表 2 和 3，第二个宏(下)是在复制到工作表 2 和 3 后从工作表 1 中删除原始信息。

Sub Clear()

    Range("A2:D2").Select
    Selection.ClearContents
    Rows("3:500").Select
    Selection.ClearContents
    Range("A2").Select

End Sub

如果有人可以为我提供解决方案，将不胜感激。

问候

Answer 1

如果您只有一个工作表，然后放置

Call clear()

无论你想在哪里运行 clear sub 都可以实现这一点。

但是，如果您有多个工作表，则需要在清除单元格之前和之后在 Clear() 子中指定它们。