How to combine 2 macro into 1

Question

Would it be possible for someone to advise how to combine the following 2 Macros into 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

The first Macro (above) is to copy information from Sheet 1 to Sheet 2 & 3 and the second Macro (below) is to delete the original information from Sheet 1 after copied over to Sheet 2 & 3.

Sub Clear()

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

End Sub

Much appreciated if someone could provide me with a solution.

Regards

You are showing us a total of three subs and one function. So, which subs / function do you wish to combine? But if you do not know yet how to combine macros then you might want to start learning VBA and have a look here: http://www.homeandlearn.org/ — Ralph, Jun 05 '16 at 22:22
I recommend reading through [How to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Your code can quickly be updated by simply deleting the `.selection` back to the range before `Select`, if that makes sense — BruceWayne, Jun 06 '16 at 03:26

score 2 · Answer 1 · answered Jun 06 '16 at 07:08

If you only have one worksheet then placing

Call clear()

wherever you want to run the clear sub will achieve this.

However if you have more than one worksheet, you'd need to specify them in the Clear() sub before and after completion of clearing the cells.

score 1 · Accepted Answer · answered Jun 05 '16 at 22:39

You have a sub that calls a sub that calls a function. I assume you just want the clear() sub to be part of the first sub. Just add the contents of the clear sub to the first ArchiveReminder() sub.

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

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

End Sub

I wasn't really looking that much. Whatever you say is probably right. Anyway, I voted to close this post because this post is of no help to any programmer (and as such not relevant to StackOverflow). — Ralph, Jun 05 '16 at 22:46
You would be surprised who it might help in the future. There are millions of people "tinkering" with VBA. Somebody will have the same question some day. SO has great SEO so when people Google that same sort of question this could come up. There are lots of good free Excel VBA tutorials out there. Google that and go through one. Keep learning... — MatthewD, Jun 05 '16 at 22:52

How to combine 2 macro into 1

2 Answers2