CODEWITHSUNDEEP
vbaexcel
Fabrizio Martinez
Fabrizio Martinez

Reputation: 61

VBA Set = Worksheets on a different workbook

I'm going crazy over this one. I'm trying to filter the first column on Sheet "Detail Aging (2)", but the filter values vary with time and I'm required to not modify the Macro in the future.

I thought of pulling the filter values from a sheet in a different workbook using a range, but I can't reference the sheet in that workbook. I get "Subscript out of range".

Below is the piece of code I'm using. It works just fine when the sheet with the range is on the same workbook.

Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Detail Aging (2)")
Set wsL = Worksheets("C:\Users\lm733600\Desktop\Fabrizio\[Collectible Blank.xlsm]Collectors")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("ColRange")

Any piece of advice will be greatly appreciated!

Upvotes: 4

Views: 12229

Answers (1)

MatthewD
MatthewD

Reputation: 6801

Use a workbook object

Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Detail Aging (2)")

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\Users\lm733600\Desktop\Fabrizio\Collectible Blank.xlsm", ReadOnly:=True)

set wsL = wb.Sheets("Sheet1")

Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("ColRange")

Upvotes: 3

Related Questions