VBA code : Copy data multiple worksheet data (in date range) to one specific worksheet

[Mckellar1983]

Good day

Im currently busy updated the way we use logsheets and combining all those date to one destination workbook. We copy all the information from prevouis date and paste it into the destionation workbook. The problem is that there 10 logsheets that needs to be copied over to the destionation workbook, but it takes alot of time that I dont have. I want to automate this process by using VBA to do so

Fields that we have
DateReceived DateActioned Company ..... until Column AB:AB
We also validate the information for any empty cells and correct date format.

In Destination workbook
1) We add empy row before we copy the information over from source workbook
2) We copy each staff member logsheet to the destionation worksheet (Almost summary workbook)
3) After all the log sheets are copied we format the DateReceived and DateActioned to short date format
4) The last step is to upload it to PowerBI

Is there any way to create VBA code that will automate this process for multiple source workbook to one destination workbook.

Kind regards