Retrieve Data from Multiple Closed Workbooks

In cases where one need to retrieve and compile data from multiple closed workbooks, there are usually 2 situations: 1 being to retrieve Data from a FIXED cell location; 1 being to fetch Data from a varying cell location (etc Grand total as per below example)

The attached DataFromClosedWKB file consists of 3 tabs.

1st Scenario: retrieve Data from a FIXED cell location

FetchData Sheet is for Retrieving values from FIXED cell Location (etc Budget figure as per left hand side eg with fixed location of C4)

2nd Scenario: retrieve Data from a varying cell location

FetchData2 Sheet is for Retrieving values from Sheets of a structured format ~ Consistent columns (etc Column B for the "total" label and column D for values in screenshot to the left) but with varying cell Location (depending on location of last Total)

AggregateTotal sheet is for Retrieving values from Variable Location (irregardless of values appearing in which columns) like Grand Total (Cell Location depends on number of transactions, retrieves data based on the last occurrence of the word "total")

This is abit more flexible than FetchData2 but slower as the macro involves opening of the selected files in the backend.

Download File