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()?

 

[DanteAmor]

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.

Do you have your book enabled for macros?
Then try this:

Private Sub Workbook_Open()
  MsgBox "Hello"
End Sub

IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.

 

[Bandito1]

Hey,

Sorry for late reply, i had vacation.

My workbook is filled with macro's and yes, macro's are enabled

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]  MsgBox "Hello" [COLOR=#333333]End Sub[/COLOR]

This in thisworkbook works.
When i open the workbook i see the messagebox with "Hello"

 

[DanteAmor]

Hey,

Sorry for late reply, i had vacation.

My workbook is filled with macro's and yes, macro's are enabled

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]  MsgBox "Hello" [COLOR=#333333]End Sub[/COLOR]

This in thisworkbook works.
When i open the workbook i see the messagebox with "Hello"

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:

Code:
Sub Autpen()

Workbooks("Persnlk.xls").Close SaveChanges:=False
Workbooks("VB_Macros.xls").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]

Hey again,

It works, noticed the problem is somewhere else in my file.

Made a post about it.

Thanks!

 

[DanteAmor]

Hey again,

It works, noticed the problem is somewhere else in my file.

Made a post about it.

Thanks!

I'm glad to help you. Thanks for the feedback.