Simple report generation by copy-pasting ranges and tables

genreport.bas

Sub genreport()
    Dim wb As Workbook
    Set wb = Application.Workbooks.Add
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("mainsheet").ListObjects("maintable")
    Dim i, lastrow As Integer
    lastrow = 1
    For i = 1 To tbl.ListRows.Count
        Dim val As String
        val = Trim(tbl.ListColumns("Source").DataBodyRange(i, 1).Value)
        'copy a range or table
        If InStr(val, "!") Then
            Dim wsname, rng As String
            wsname = Left(val, InStr(val, "!") - 1)
            rng = Right(val, Len(val) - InStr(val, "!"))
            ThisWorkbook.Worksheets(wsname).Range(rng).Copy
        Else
            ThisWorkbook.Worksheets(val).ListObjects(val).Range.Copy
        End If
        'paste values and formats
        With wb.Sheets("Sheet1").Range("A" & lastrow)
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
        End With
        'set last row to be +2
        With wb.Sheets("Sheet1")
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 2
        End With
    Next i
    wb.Sheets("Sheet1").Cells.EntireColumn.AutoFit
End Sub