VBA to filter a worksheet in a different workbook

[CTRDevelopments]

Hi, I have a master excel document which holds all of the VBA code. I need this to open up a different excel BOM and remove some lines from it. I have the code open up the second excel file but the filering and row deletion code (looking in this case for a row in column c containing "Roof Plan", seem to try to filter the master excel which clearly goes badly wrong. I am not quite sure what I have done wrong here as I have tried to set the BOM file as the active worksheet etc.

If I copy the code into the BOM file it works as intended so I can only assume it is something to do with the vba residing on the master file not the BOM file.

Any help is much appreciated

cheers

Dim MyFilePath As String
Dim FileName As String
Dim FullFileName As String

MyFilePath = ThisWorkbook.Path
FileName = "\Drawings\Complete_BOM.xlsm"
FullFileName = MyFilePath & FileName

Set WkB = Workbooks.Open(FullFileName)
Workbooks("Complete_BOM.xlsm").Activate
Worksheets("Sheet1").Activate

    With ActiveSheet
        .AutoFilterMode = False
        With Range("c1", Range("c" & Rows.Count).End(xlUp))
            .AutoFilter 1, "Roof Plan"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With

 

[Fluff]

Hi & welcome to MrExcel.
Is that code in a standard module?

 

[CTRDevelopments]

No, I dont think so. I think it is just inside a button I have put on the excel page. Never really known what the difference is

 

[Fluff]

If you double click on sheet4(Aisle Configurator) is the code located in the code module that opens up?

 

[CTRDevelopments]

yes, sorry I thought you meant a module as in 'insert - module'

 

[Fluff]

If you move it to a standard module (ie what you said above) then the code should work, alternatively you could use

Dim MyFilePath As String
Dim FileName As String
Dim FullFileName As String

MyFilePath = ThisWorkbook.Path
FileName = "\Drawings\Complete_BOM.xlsm"
FullFileName = MyFilePath & FileName

Set wkb = Workbooks.Open(FullFileName)

    With wkb.Sheets("Sheet1")
        .AutoFilterMode = False
        With .Range("c1", .Range("c" & Rows.Count).End(xlUp))
            .AutoFilter 1, "Roof Plan"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With

 

[CTRDevelopments]

Superb, many thanks, I slotted in your code and works perfectly. Many thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CTRDevelopments]

Can I ask, you added a couple of .'s and changed. Clearly it worked, but why? (Just wanting to understand )

Set wkb = Workbooks.Open(FullFileName)

With wkb.Sheets("Sheet1")

 

[Fluff]

The full stop in front the each Range ties the range to the with statement above, so it only works on that sheet.
Normally if you don't use them it will work on the active sheet. However if the code is in a sheet module (which it is) any unqualified ranges will work on that sheet (in this case the Aisle config sheet) & not the active sheet.
Hope that makes sense