» Copy a range with more Areas to a Database sheet using VBA in Microsoft Excel

• The example codes will copy to a database sheet with the name Sheet2.
• Every time you run one of the subs the cells will be placed below the last row with data or after the last Column with data in sheet2.
• For each example there is a macro that does a normal copy and one that is only Copy the Values.
• The Example subs use the functions below (the macros won’t work without the functions).

Sub CopyMultiArea()
    Dim destrange As Range
    Dim smallrng As Range
    For Each smallrng In Sheets("Sheet1"). _
        Range("a1:c10,e12:g17").Areas
        Set destrange = Sheets("Sheet2").Range("A" & _
        LastRow(Sheets("Sheet2")) + 1)
        smallrng.Copy destrange
    Next smallrng
End Sub

Sub CopyMultiAreaValues()
    Dim destrange As Range
    Dim smallrng As Range
    For Each smallrng In Sheets("Sheet1"). _
        Range("a1:c10,e12:g17").Areas
        With smallrng
            Set destrange = Sheets("Sheet2").Range("A" & _
            LastRow(Sheets("Sheet2")) + 1).Resize( _
            .Rows.Count, .Columns.Count)
        End With
        destrange.Value = smallrng.Value
    Next smallrng
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
    On Error Resume Next
    Lastcol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function