Code not executing

[Bandito1]

Hi all,

I use this code to filter data in my workbook;

Private Sub Workbook_Open()On Error GoTo Errorhandler
Workbooks("Persnlk.xls").Close SaveChanges:=False
Workbooks("VB_Macros.xls").Close SaveChanges:=False


Worksheets("Shifts").AutoFilterMode = False


Worksheets("Shifts").Range("J1:Q" & Range("J" & Rows.Count).End(xlUp).Row).ClearContents


Worksheets("Shifts").Range("A1").AutoFilter Field:=2, Criteria1:="<>"
Worksheets("Shifts").Range("A1").AutoFilter Field:=3, Criteria1:="<>"


Worksheets("Shifts").AutoFilter.Range.Copy


Worksheets("Shifts").Range("J1").PasteSpecial xlPasteValues


Application.CutCopyMode = False


Worksheets("Shifts").AutoFilterMode = False


Exit Sub
Errorhandler:
End Sub

I notice that this code is not working under Workbook_Open()

When i paste it in a module and press on an button it works.

I would like to make this code work when i open the workbook. Anyone knows why it's not working under workbook_Open()?

 

[Rick Rothstein]

I think you have placed the code in the wrong module. Go into the VBA editor, look to the left side and find the window titled Project-VBAProject... it has a list of the sheets in your workbook... look at the bottom of that list for the entry labeled ThisWorkbook... double click it and place your Workbook_Open code procedure in the code window that just opened up (make sure to remove that code from any other modules you put it in). That's it... save your workbook and the next time you open it, your code should run.

 

[Fluff]

On top of what Rick has said, if these two workbooks

Workbooks("Persnlk.xls").Close SaveChanges:=False
Workbooks("VB_Macros.xls").Close SaveChanges:=False

are not open when the code runs. Then nothing will happen

 

[DanteAmor]

You can put the code in a normal module, but the macro should be called "Autpen".
Make the following changes:

Sub Auto_Open()
[COLOR=#ff0000]    On Error Resume Next[/COLOR]
    Workbooks("Persnlk.xls").Close SaveChanges:=False
    Workbooks("VB_Macros.xls").Close SaveChanges:=False
[COLOR=#ff0000]    On Error GoTo 0[/COLOR]
[COLOR=#ff0000]    If Worksheets("Shifts").AutoFilterMode Then Worksheets("Shifts").AutoFilterMode = False[/COLOR]
    Worksheets("Shifts").Range("J1:Q" & Range("J" & Rows.Count).End(xlUp).Row).ClearContents
    Worksheets("Shifts").Range("A1").AutoFilter Field:=2, Criteria1:="<>"
    Worksheets("Shifts").Range("A1").AutoFilter Field:=3, Criteria1:="<>"
    Worksheets("Shifts").AutoFilter.Range.Copy
    Worksheets("Shifts").Range("J1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Worksheets("Shifts").AutoFilterMode = False
End Sub

If an error occurs, write here the error message and the macro line where it stopped.

 

[Bandito1]

You can put the code in a normal module, but the macro should be called "Autpen".
Make the following changes:

Sub Auto_Open()
[COLOR=#ff0000]    On Error Resume Next[/COLOR]
    Workbooks("Persnlk.xls").Close SaveChanges:=False
    Workbooks("VB_Macros.xls").Close SaveChanges:=False
[COLOR=#ff0000]    On Error GoTo 0[/COLOR]
[COLOR=#ff0000]    If Worksheets("Shifts").AutoFilterMode Then Worksheets("Shifts").AutoFilterMode = False[/COLOR]
    Worksheets("Shifts").Range("J1:Q" & Range("J" & Rows.Count).End(xlUp).Row).ClearContents
    Worksheets("Shifts").Range("A1").AutoFilter Field:=2, Criteria1:="<>"
    Worksheets("Shifts").Range("A1").AutoFilter Field:=3, Criteria1:="<>"
    Worksheets("Shifts").AutoFilter.Range.Copy
    Worksheets("Shifts").Range("J1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Worksheets("Shifts").AutoFilterMode = False
End Sub

If an error occurs, write here the error message and the macro line where it stopped.

Hey,

How do i run this module when i open the workbook?

 

[Bandito1]

On top of what Rick has said, if these two workbooks

Workbooks("Persnlk.xls").Close SaveChanges:=False
Workbooks("VB_Macros.xls").Close SaveChanges:=False

are not open when the code runs. Then nothing will happen

The 2 workbooks are closed yes. That explains why nothing happens then

 

[DanteAmor]

Hey,

Does this code auto run when i open my workbook?
Or do i have to call it somewhere?

That's right, the code runs when you open the book. Do the test.

 

[Bandito1]

It doesn't run.

Workbooks("Persnlk.xls").Close SaveChanges:=False
    Workbooks("VB_Macros.xls").Close SaveChanges:=False

This doesn't work for example. The files stay open.
This makes me think the rest also doesn't work.

When i place the module under a button and i press it, it works.

 

[DanteAmor]

It doesn't run.

Workbooks("Persnlk.xls").Close SaveChanges:=False
    Workbooks("VB_Macros.xls").Close SaveChanges:=False

This doesn't work for example. The files stay open.
This makes me think the rest also doesn't work.

When i place the module under a button and i press it, it works.

Try this, close de book an reopen.

Sub Auto_Open()
  MsgBox "hi, I am running!"
End Sub

If the books are open and you want to close them, they must be in the same excel application. Check the names of books and extensions.


Now try this:

Sub Auto_Open()
    
    Workbooks("[COLOR=#ff0000]Persnlk.xls[/COLOR]").Close SaveChanges:=False
    Workbooks("[COLOR=#ff0000]VB_Macros.xls[/COLOR]").Close SaveChanges:=False
    
    If Worksheets("Shifts").AutoFilterMode Then Worksheets("Shifts").AutoFilterMode = False
    Worksheets("Shifts").Range("J1:Q" & Range("J" & Rows.Count).End(xlUp).Row).ClearContents
    Worksheets("Shifts").Range("A1").AutoFilter Field:=2, Criteria1:="<>"
    Worksheets("Shifts").Range("A1").AutoFilter Field:=3, Criteria1:="<>"
    Worksheets("Shifts").AutoFilter.Range.Copy
    Worksheets("Shifts").Range("J1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Worksheets("Shifts").AutoFilterMode = False
End Sub

If an error occurs, write here the error message and the macro line where it stopped.

 

[Bandito1]

I tried, no messagebox is showing.

Maybe i made the module wrong?

I just did Insert -> Module and changed it's name to Autpen. Nothing more nothing less.