Macro to copy multiple tabs into master tab

[ramridd]

Hi guys looking to create a macro that first clears data from tabs A2 – AS2 all the way down in my master tab then copies data from 3 different tabs and paste them into that master tab with all records. For this example I want to copy cell A2 – AS2 to about 20000 rows down from tabs Wine, Dinner, and Food into my master tab.

I’ve tried creating this macro by recording it but the problem with that is the recording sticks to exact parameters. So for example if I add more records to one of my tabs the macro will paste over records.

I need the macro to paste all records from the first tab in master then start pasting in the next blank row for the second tab as the amount of rows in my tabs will constantly be changing.

I’m not the best at currently working with macros wondering if anyone could help. Thank you.

[Vraag en antwoord]

Make proper use of tables. The code is not that difficult. For the columns AV:BA you will have to enter the formulas again.

Sub VenA()
  With Sheets("Master").ListObjects(1)
    If .ListRows.Count Then .DataBodyRange.Delete
    For Each sh In Sheets
      If sh.Name <> .Parent.Name Then
        ar = sh.ListObjects(1).DataBodyRange
        .ListRows.Add.Range.Resize(UBound(ar), UBound(ar, 2)) = ar
      End If
    Next sh
  End With
End Sub

[ramridd]

Is there anyway I could keep the formulas? Basically I'm trying to create it in a way where the end user only has to push a button or two as they are not the most versed in excel.

[Vraag en antwoord]

You only have to re-enter the formulas once. These are remembered in the table.

[ramridd]

Awesome. Thank you!