VBA Reference other workbook

[dturgel]

Hi,

I'm moving a pricing worksheet from inside my workbook to an external workbook. I have the following lines of code that I want to turn into a reference from internal to external. I know the general idea is to use [external]! format but every time I have tried to do that in the past in VBA I've failed. Can someone please help me out here?

Sheets("Mkt curve").Select
ActiveWorkbook.Worksheets("Mkt curve").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Mkt curve").Sort.SortFields.Add
ActiveWorkbook.Worksheets("Mkt curve").Sort
ActiveCell.FormulaR1C1 = "='Mkt curve'!R[-4]C[155]"


Is it as simple as just turning them into the following?

Sheets([2012-2016 bound and quoted register]"Mkt curve").Select
ActiveWorkbook.Worksheets([2012-2016 bound and quoted register]"Mkt curve").Sort.SortFields.Clear
ActiveWorkbook.Worksheets([2012-2016 bound and quoted register]"Mkt curve").Sort.SortFields.Add
ActiveWorkbook.Worksheets([2012-2016 bound and quoted register]"Mkt curve").Sort
ActiveCell.FormulaR1C1 = "='[2012-2016 bound and quoted register]Mkt curve'!R[-4]C[155]"

I feel like I'm missing something.

Thanks!

Daniel

 

[yky]

[external]! only works in sheets. For VBA, you need something like:

WorkBooks("MyBookName").Sheets("MySheetName").Select

 

[dturgel]

yky, thanks!

I tried the code but when the macro ran it tripped over the following line - does anything look wrong to you in how I applied this to my specific workbooks/sheets?

Workbooks("2012-2016 bound and quoted register").Sheets("Mkt curve").Select

Daniel

 

[yky]

Try adding extension, most likely ".xlsx" or ".xlsm", to the name of the workbook. You need to use the full name, including extension, of the file.