VBA Copying data from multiple worksheets into a master worksheet

[vba beg]

Hello,

I'm trying to copy data from multiple workbooks into a one worksheet.

Essentially, I will receive multiple workbooks that contains a single sheet of data (data will be consistently located in B3:B6) and the name of the sheet will be different. I want to consolidate this data into one worksheet.

ie
Copy details from the first workbook (data located B3:B6) and paste it into a master workbook in (first data set to be pasted in A1:A4, second set of data pasted in B1:B4 and so on) and repeat this through all the selected files

Thanks in advance

 

[pjmorris]

If your workbooks are all in the same folder then use Powerquery: from the New Query, on the Get & Transform tab of the Data ribbon choose 'Get File', 'From Folder'. You'll then be asked to navigate to the folder and choose 'Edit'. It also means you can add additional workbooks simply by saving them in the folder and refreshing the workbook that collates them all.

Not the VBA solution you asked for, but much more powerful.

 

[vba beg]

I'm sorry I'm not able to find this/might not have access

 

[pjmorris]

What version of Excel do you have? Unless it’s before 2010 you will have it, but it might be called something different. Have a look at this link: (it’s definitely there) https://www.myonlinetraininghub.com/power-query-version-compatibility-and-installation

 

[vba beg]

I managed to download the power query but this seems more complex that vba (maybe it's just me). I've managed to figure out the vba code to open multiple files but the issue that I'm now having is simply to copy the data from range B3:B6 from each workbook and pasted in in A1:A4

 

[pjmorris]

It is different to via, but for what you’re trying to achieve Powerquery is so much better - definitely worthwhile exploring. There’s quite a bit available on line, my go to reference if ‘M is for (Data) Monkey’. Good luck