I am very new to VBA and need a solution that would allow me to automatically compile the data from multiple tables in multiple sheets into one. To get into more detail:

I have 5 tabs with tables in the same format. I want the data to be compiled into one, already existing sheet. I want only the data to be copied, without the headers. I would like the sheet with the compiled data to be cleared out each time I run the macro to avoid having duplicates. The data in each table starts in the third row. The range of the data will change dynamically with each input, but the amount of columns will remain the same. I would like to have the data copied one under the other without any gaps in between.

Is that doable? There aren’t any other conditions when it comes to copying the data but I couldn’t figure it out on my own. Thanks in advance.

here’s the last code I tried using:
Sub ConsolidateData()
Dim ws As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim destRow As Long
Dim rng As Range
Dim sourceSheets As Variant
Dim sheetName As Variant
‘
sourceSheets = Array(“Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”) ‘
‘ Define the destination sheet
Set destSheet = ThisWorkbook.Sheets(“Projects consolidated”)
‘ Clear existing data from the destination sheet, starting from row 3
destSheet.Rows(“3:” & destSheet.Rows.Count).ClearContents
‘
destRow = 3
‘
For Each sheetName In sourceSheets
Set ws = ThisWorkbook.Sheets(sheetName)
‘
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
‘
If lastRow > 2 Then
‘ Define the range to copy (all columns, from row 3 to last row)
Set rng = ws.Range(“A3:Z” & lastRow) ‘
‘
rng.Copy Destination:=destSheet.Cells(destRow, 1)
‘
destRow = destRow + rng.Rows.Count
End If
Next sheetName
End Sub