VBA Advanced Filter works in local Workbook, but not when placed in Personal.xlsb

[birosd]

This code works in the local Workbook perfectly, but I can not figure out how (and I tried a couple of suggestions here on S.O.F.) to get it to work in the Personal.xlsb file. Any help is much appreciated.

'This macro works locally - need to get it to work at the Personal.xlsb level
Sub AdvLookupVals()

   Dim colA_Range As Range
   Dim rngLookupRange As Range
   Dim rngFoundRange As Range
   Dim wb As Workbook

  ThisWorkbook.Worksheets("Sheet1").Activate

   'when you do this, Lookup.xls becomes active and sets the range
   Set wb = Workbooks.Open("C:\Lists\Resource_Lookup.xlsx", True, True) ' open the source workbook, read only
   Set rngLookupRange = wb.Worksheets("Sheet1").Range("A1:A656")

   'Makes target workbook active again
    ThisWorkbook.Activate

   'Set rngFoundRange = wb.Worksheets("Sheet1").Range("A1:A653")
   'rngFoundRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= '_   'hoping this would make the macro work in Personal.xlsb

       Range("A1:A653").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        rngLookupRange, Unique:=False                                               'rngLookupRange gets the criteria from above. .filters by name.

   'releases resources back to the system and close the other workbook
   Set rngLookupRange = Nothing
   wb.Close False ' close the source workbook without saving any changes
   Set wb = Nothing ' frees memory

 End Sub

 

[Fishboy]

This code works in the local Workbook perfectly, but I can not figure out how (and I tried a couple of suggestions here on S.O.F.) to get it to work in the Personal.xlsb file. Any help is much appreciated.

'This macro works locally - need to get it to work at the Personal.xlsb level
Sub AdvLookupVals()

   Dim colA_Range As Range
   Dim rngLookupRange As Range
   Dim rngFoundRange As Range
   Dim wb As Workbook

  ThisWorkbook.Worksheets("Sheet1").Activate

   'when you do this, Lookup.xls becomes active and sets the range
   Set wb = Workbooks.Open("C:\Lists\Resource_Lookup.xlsx", True, True) ' open the source workbook, read only
   Set rngLookupRange = wb.Worksheets("Sheet1").Range("A1:A656")

   'Makes target workbook active again
    ThisWorkbook.Activate

   'Set rngFoundRange = wb.Worksheets("Sheet1").Range("A1:A653")
   'rngFoundRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= '_   'hoping this would make the macro work in Personal.xlsb

       Range("A1:A653").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        rngLookupRange, Unique:=False                                               'rngLookupRange gets the criteria from above. .filters by name.

   'releases resources back to the system and close the other workbook
   Set rngLookupRange = Nothing
   wb.Close False ' close the source workbook without saving any changes
   Set wb = Nothing ' frees memory

 End Sub

Hi birosd,

If you move this macro into a personal workbook instead of keeping it in the target workbook then you cannot use "ThisWorkbook" as a reference without the code trying to update the sheets in your personal workbook. I suspect you probably need to do something like:

Dim wb2 as Workbook
Set wb2 = Workbooks("Target Workbook Name.xlsx")

You would also need to replace any part of your code that says "ThisWorkbook" with wb2, so for example this line:

ThisWorkbook.Worksheets("Sheet1").Activate

Would become this:

wb2.Worksheets("Sheet1").Activate

 

[birosd]

Thanks for the suggested changes, Fishboy. Although, 438 runtime errors came up with hitting the new updates at (both at the workbook and Personal.xlsb levels):

wb.Worksheets("Sheet1").Activate

and

wb2.Wooksheets("Sheet1").Active

in place of

ThisWorkbook.Worksheets(Sheet1).Activate

 

[Fishboy]

Thanks for the suggested changes, Fishboy. Although, 438 runtime errors came up with hitting the new updates at (both at the workbook and Personal.xlsb levels):

wb.Worksheets("Sheet1").Activate

and

wb2.Wooksheets("Sheet1").Active

in place of

ThisWorkbook.Worksheets(Sheet1).Activate

Hi again birosd,

Are you able to share example copies of the 2 workbooks for us to test with (feel free to obscure any confidential data by either replacing it with xxxxxxx's or by swapping it out for made up data instead? If you can share then you will need to upload copies to a file hosting site such as Drop Box, One Drive, Google Drive or similar then share a link to the file(s) in a forum post.

 

[birosd]

Thanks for the offer of additional help, Fishboy. I had to get the task done that this code was intended to do. So I had a little spark of inspiration and took a different route. Instead, used some code running from my Personal.xlsb that populated the first empty column of the active sheet with a Vlookup addressed to the closed workbook source. It then places an Autofilter on the data and filters for what I need, then it removes the additional column when finished.