Excel Macro - Union of table

0
votes

I am trying to run a macro that copy three tables from different worksheets and paste it together in a new worksheet.

The number of rows in the tables are not always the same. Therefore, I need a macro with a 'dynamic' "LastRow" parameter so that every time I update one single table the result of the macro is updated.

I tried to run this macro:^

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("Discussed Files").Select
Range("Table1[#Headers]").Select
Selection.Copy
Sheets("All data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Discussed Files").Select

Range("Table1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All data").Select
Range("A2").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Files within 3 Days").Select

Range("Table3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All data").Select
Range("A" & lastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Files 10.04.17").Select

Range("Table5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All data").Select
Range("A" & lastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$Y$" & lastRow), ,     xlYes).Name = _
    "Table14"
Range("Table14[#All]").Select
ActiveSheet.ListObjects("Table14").TableStyle = "TableStyleMedium2"

I cannot understand exactly what the macro is doing. It ends up woth a table having number of rows equal to first sheet but data inside the table are 'randomly' taken from the other sheets. Moreover, the selection to make the result a table is not working properly.

2
excelvba
For starters, you define lastrow before you paste anything so it is not up to date as it were (and it will need to be updated as you add more data). Also you should specify a sheet as it's not clear which it is referencing. - SJR

2 Answers

2
votes

As per comment above (have also removed unnecessary Selects)

Sub x()

Dim lastRow As Long

With Sheets("All data")
    Sheets("Discussed Files").Range("Table1[#All]").Copy
    .Range("A1").PasteSpecial Paste:=xlPasteValues

    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Files within 3 Days").Range("Table3").Copy
    .Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues

    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Files 10.04.17").Range("Table5").Copy
    .Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues

    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .ListObjects.Add(xlSrcRange, .Range("$A$1:$Y$" & lastRow), , xlYes).Name = "Table14"
    .ListObjects("Table14").TableStyle = "TableStyleMedium2"
End With

End Sub
1
votes

You don't update lastRow between steps, so you are basically pasting them one over another into same spot because the lastRow does not update after you paste one of your tables, it retains the same value from the beginning of your code in each:

Range("A" & lastRow).Select
Selection.PasteSpecial

Also, this code will return last row with data in it so if you are pasting into clean sheet, you are pasting all tables into the same spot:

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

EDIT: 

Dim lastRow As Long
lastRow = Sheets("All data").Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("Discussed Files").Range("Table1[#All]").Select
Selection.Copy
Sheets("All data").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

lastRow = Sheets("All data").Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("Files within 3 Days").Range("Table3").Select
Selection.Copy
Sheets("All data").Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

lastRow = Sheets("All data").Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("Files 10.04.17").Range("Table5").Select
Selection.Copy
Sheets("All data").Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

lastRow = Sheets("All data").Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("All data").ListObjects.Add(xlSrcRange, Range("$A$1:$Y$" & lastRow), , xlYes).Name = _
    "Table14"
Range("Table14[#All]").Select
Sheets("All data").ListObjects("Table14").TableStyle = "TableStyleMedium2"