Range reference to select entire worksheet & dynamically link worksheet name

[mrdowjones]

Hi,

I have the below code that opens and copies data from a closed workbook to the active workbook.

Sub FetchData()


    Dim src As Workbook
    
    Set src = Workbooks.Open("G:\Managers\Existing Managers\Equities\Data Management\Monthly Manager Research Notes\PA_OUTPUT\FACTSET_MANAGER_DATA.xlsx", True, True)
    
    ThisWorkbook.Activate
    
    Worksheets("holdings_data").Range("a1").Formula = src.Worksheets("00161").Range("A1").Formula
    
    src.Close
    


End Sub

I would like to change two things which I cannot figure out in the final piece of the code:

src.Worksheets("00161").Range("A1").Formula

1: Worksheets("00161"): I would like this to be dynamic, so that I can enter 00161 to a cell in my active destination workbook and change it on demand.
2: Range("A1").Formula: I would like to copy the entire used range in this worksheet.

I have failed at attempts on both of the above. Does anyone have any ideas?

Many thanks!

 

[steve the fish]

Soemthind like this perhaps. Bear in mind that you should probably clear the target worksheet each time or you could have residual formulas in there:

Dim shName As String, src As Workbook, sh As Worksheet, addr As String

shName = "00161"
    
Set src = Workbooks.Open("G:\Managers\Existing Managers\Equities\Data Management\Monthly Manager Research Notes\PA_OUTPUT\FACTSET_MANAGER_DATA.xlsx", True, True)
Set sh = src.Sheets(shName)
addr = sh.UsedRange.Address
ThisWorkbook.Sheets("holdings_data").Range(addr).Formula = sh.Range(addr).Formula
src.Close False

 

[mrdowjones]

Thanks Steve,

Any ideas on how I would make the "00161" dynamic and linked to a cell in the workbook?

Thanks,
Antony