Many thanks for the prompt reply. By 'consolidate' I mean merely copy.

Let me describe the structure of each file: Workbook name: XXX_report Worksheets within each Workbook: 'Extractions', 'Report', 'Instructions' In part 1 I need to copy (only values) from 'Report' sheet cells from G2 to L2 for each XXX_Report and paste them into a unique blank worksheet. In part 2 I need to copy (normal copy) from 'Report' sheet from column A to column F for each XXX_Report and paste into a unique blank worksheet (rows goes from 4 to??? The best music production software for mac. - it changes). Would be grateful if you could confirm whether the above clarifies. Thanks and regards. I guess it could help but I would have been a bit more inclined towards a VBA solution.

Show formulas in the cells of an Excel spreadsheet to make it easy to find cells that contain formulas, or to check for errors. You can choose to make formulas visible, and then print your worksheet as you normally would. Show and print formulas. Excel for Office 365 for Mac, Excel 2019 for Mac, Excel 2016 for Mac.

The one with formula means I should have all the Workbooks I need to retrieve data from open? Or they can just be within the same folder? Then, within the above formula scenario, I think I should have something like the following. But in case the below is correct. Is there anyway i can modify workbook names automatically or i need to enter all of them manually?

Hope it is not a silly Q. Thanks a ton in advance A2 = [001_report.xlsm]Report!$G$2 B2 = [001_report.xlsm]Report!$H$2 C2 = [001_report.xlsm]Report!$I$2 D2 = [001_report.xlsm]Report!$J$2 E2 = [001_report.xlsm]Report!$K$2 F2 = [001_report.xlsm]Report!$L$2 A3 = [002_report.xlsm]Report!$G$2 B3 = [002_report.xlsm]Report!$H$2 C3 = [002_report.xlsm]Report!$I$2 D3 = [002_report.xlsm]Report!$J$2 E3 = [002_report.xlsm]Report!$K$2 F3 = [002_report.xlsm]Report!$L$2. I'm not sure what you want in. You have several workbooks (called 'source books'). Each of these workbooks has one sheet of interest (all with the same worksheet name?). The number of columns and row on the sheet of interest varies from workbook to workbook.

You have a Master Workbook and want one tab in the Master Book for each of the several workbooks. Each of those sheets in the Master will mirror the sheet of interest in each source book. Is that the problem?

Will the number of source books change over time? Do you want that automaticaly accounted for by the Master Book? If so, how will it know when a new source book is created? Code: Sub test() Dim uiVal As Variant Dim oneWBName As Variant, wbPart As Variant Dim oneWB As Workbook, oneCell As Range Dim writeCell As Range, i As Long Const SheetName As String = 'Sheet1' Const inputAddress As String = 'G2:L2' Do uiVal = Application.GetOpenFilename(MultiSelect:=False): Rem adjust to True????

A common thing to do in Excel is to use one sheet for collecting raw data, such as sales totals, and then to use another tab, or even a workbook to create summations or reports; the thing is though, doing so requires you link the cells first. Fortunately, this is a lot easier than it sounds. To see how it works, first create or open a sheet that has some data in it; here, we’ll use the data sheet shown below: Highlight and copy the cells you want to link, in this case, the YTD totals for each of the prior ten years. Next, open a new Sheet by clicking one of the tabs at the bottom of the workbook or by clicking on the little plus icon.