getting rows from an external worksheet

[Omiddy]

hello,
I am a first time poster on MrExcel and I hope i dont sound too much like a nooby.
I have done a lot of research and tried various different methods but I haven't found an efficient way to accomplish what I am trying to do.
I have an excel file with a number of different sheets, which will be updated frequently and new rows will be added from time to time. I am trying to make another excel file to get these rows. I want it to copy/fetch every row in the worksheets, but only certain columns. I want it to be done in order. for example, it writes every row from sheet 1, followed by every row in sheet 2, and so on. If later a row is added to sheet 1, I need it to be updated on the new excel sheet.

I hope this makes sense and I would appreciate any help.

 

[Derek Brown]

Hello and welcome to The Board.
I would consider two possible methods but it depends on your experience and version of Office that is being used.
The first is to used VBA (or a macro) to open the other workbook and copy the required columns from each worksheet to the 'master' workbook. If there are worksheets that should be ignored then I would consider listing the required worksheets in a table in your 'master' worksheet and referencing that in your code.
The other option would be to use Microsoft Access. Here I would have each required worksheet as a linked table in the database. This would only work if the workbook name and location will not change. I would then create a UNION query to 'append' the required columns from each linked table and then that query would become an external table connection in your 'master' workbook - this could be set to refresh on opening the workbook.
However - it does depend on how many worksheets and rows of data are involved - the second option could take a long time to refresh.