Range reference to select entire worksheet & dynamically link worksheet name

mrdowjones

New Member
Joined
Feb 5, 2019
Messages
2
Hi,

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

Code:
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:

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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

Code:
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
 
Upvote 0
Thanks Steve,

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

Thanks,
Antony
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top