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.
我都快疯了。我在过滤第一行"细节老化(2)",但是过滤器的值会随着时间的变化而变化,所以我以后不需要修改宏。
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!
任何建议都将非常感谢!
1 个解决方案
#1
3
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")
#1
3
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")