Sheet1 - Variable/editable cell to specify which workbook to pull data from

Hi all, I am new to the forum and relatively new to VBA. I've had success so far with the great threads on this forum, so thank you.

I believe my query is quite in depth and revolves around variable workbook lookups - and I don't know if it is possible. VBA/ Macro books are on the way, though a heads up from your experience will be greatly appreciated.

OBJECTIVE

- In Sheet1. I will be typing data in specific cells ("E4")- in this format: 07.20.15.xlsm <It is the name of a 1 sheet workbook - A report that I have created with VBA and run daily. I wish to extract data from it.


- When I have completed this task for that date, I wish to delete the cell data, and retype with 07.21.15.xlsm (picking up a later dated workbook).

- The destination for data extraction will be in my Master workbook sheet 3 (though there will be around 7 of these running at a time putting data onto incrementing sheets).


I can see theoretically it could be possible as it picks this data up

Sub Macro1()

Dim RetrieveRange As Range
Set RetrieveRange = Worksheets("Sheet1").Range("E4")
MsgBox RetrieveRange.Value

End Sub

But unsure if it useable as variable to lookup workbooks

Thanks for your help

Apart from appending the filepath name, I don't see any other reason why it could not work.

Please Login or Register  to view this content.

Perfect, thank you quekbc!