Excel for Mac: Combine Specific Cells and tabs from different Worksheets from different Workbooks into one Worksheet each respectively

[mgbb18]

I have 2 very similar goals:

1. consolidate in 1 worksheets 5 specific cells from dozens of different workbooks where each workbook contains the same 3 worksheets and the specific cells are always into the same worksheet and in the very same place;

2. consolidate in 1 worksheets 1 tab per workbook; in each workbook the tab has the same columns but different length.

Hope the above is clear but in case additional details are necessary will be happy to provide them

Thanks and regards

 

[mikerickson]

By "consolidate" do you mean sum the numeric entries in that cell or do you mean something else?
In part 1, is it the same workbooks/worksheet all the time or do those change?

 

[mgbb18]

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 ??? - it changes).

Would be grateful if you could confirm whether the above clarifies.

Thanks and regards

 

[mikerickson]

For part 1, would formulas like =[Workbook2.xlsm]Sheet1!$A$1 ,get the data you want?

 

[mgbb18]

I guess it could help but I would have been a bit more inclined towards a VBA solution.

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

 

[mikerickson]

Simple formulas like that can put data from closed workbooks. It would be easiest to set up if the workbooks were open when the formula is entered. But thereafter, it should be fine.

When I get off work, I'll try to put together a macro that will help with formula entry.

 

[mgbb18]

Many thanks Mike.
Meanwhile I'll try with the formula. I hope you have also a potential idea for part 2
Have a good one
All the best
m

 

[mikerickson]

I'm not sure what you want in #2 .
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?

 

[mikerickson]

This might help
Put this in a new workbook and run it.
You might change the Multi-Select argument of the GetOpenFile name to True. (That doesn't work on my Mac, but I coded it for ajustment to a Windows version)

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????
    
    Select Case TypeName(uiVal)
        Case Is = "String"
            uiVal = Array(uiVal)
        Case "Boolean"
            Rem user canceled
            Exit Sub
    End Select
    
    
    For Each oneWBName In uiVal
        wbPart = Split(oneWBName, Application.PathSeparator)
        wbPart = wbPart(UBound(wbPart))
        
        If WorkbookExists(CStr(wbPart)) Then
            Set oneWB = Workbooks(wbPart)
        Else
            Set oneWB = Workbooks.Open(oneWBName)
        End If
        
        If WorksheetExists(oneWB, SheetName) Then
            Set writeCell = Sheet1.Range("A65536").End(xlUp).Offset(1, 0)
            With oneWB.Sheets(SheetName).Range(inputAddress)
                For i = 1 To 6
                writeCell.Cells(1, i).Formula = "=" & .Cells(1, i).Address(, , , True)
                Next i
            End With
        Else
            MsgBox "There is no sheet " & SheetName & " in workbook " & oneWB.Path
        End If
        oneWB.Close savechanges:=False
    Next oneWBName
    
    Loop Until MsgBox("Done with that. More?", vbYesNo) = vbNo
End Sub

Function WorkbookExists(wbName As String) As Boolean
    On Error Resume Next
    WorkbookExists = (LCase(Workbooks(wbName).Name) = LCase(wbName))
    On Error GoTo 0
End Function

Function WorksheetExists(wb As Workbook, wsName As String)
    On Error Resume Next
        WorksheetExists = (LCase(wb.Sheets(wsName).Name) = LCase(wsName))
    On Error GoTo 0
End Function

 

[mgbb18]

I'm not sure what you want in #2 .
You have several workbooks (called "source books").

correct

Each of these workbooks has one sheet of interest (all with the same worksheet name?)

Yes all sheets are called "Extractions"

. The number of columns and row on the sheet of interest varies from workbook to workbook.

Number of columns is always the same BUT number of rows may vary

You have a Master Workbook and want one tab in the Master Book for each of the several workbooks.

No. I'd need to copy each tab from the various worksheet in the very same worksheet into the master workbook

Each of those sheets in the Master will mirror the sheet of interest in each source book.

Example - I have: n workbook
001_report with Extranction Sheet (it is not called "Extraction 001" but just "Extraction"); Here Extraction has 6 columns and 120 rows except the the label one
002_report with Extranction Sheet (it is not called "Extraction 002" but just "Extraction"); Here Extraction has 6 columns and 130 rows except the the label one
003_report with Extranction Sheet (it is not called "Extraction 003" but just "Extraction"); Here Extraction has 6 columns and 100 rows except the the label one

I'd need to create a unique worksheet (Master Worksheet if you wish) in my Master Workbook which includes 1 label row and 350 rows of data.

Is that the problem?

Hope the above narrow the scope of my task and lighten up what I missed earlier

Will the number of source books change over time?

Yes, it may

Do you want that automaticaly accounted for by the Master Book? If so, how will it know when a new source book is created?

I'm planning to save the source workbooks in a certain folder and create a master work book for each folder; doing that at a certain point I may need to create a master of master but i could follow the very same process saving the first level master into one folder and then create the master of master

Many thanks for your kind support