CODEWITHSUNDEEP
excelvba
paulinhax
paulinhax

Reputation: 602

Using the Aftersave event

I have this sheet where I want to make a copy of it everytime you save the original sheet.

To achieve this I was trying to use AfterSave on ThisWorkBook which calls a macro on a module.

The code is below:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

If Success Then
Application.EnableEvents = False

Call CopiarNovaPlanilha

Application.EnableEvents = True
End If
End Sub

The Application.EnableEvents is just to avoid a loop as I learnt HERE. Here'S the macro inside the module :

Sub CopiarNovaPlanilha()

ActiveWorkbook.SaveCopyAs "I:\CGP\DEOPEX\01 - Supervisão\10 - Alocação das equipes\Consulta Alocados\ALOCACAO TECNICOS.xlsx"

End Sub

This code is working perfectly although the AfterSave isn't, so when I save my original file it's not calling my sub as it should.

Anyone knows what is happening with this event?

Upvotes: 1

Views: 5509

Answers (4)

Big Cnut
Big Cnut

Reputation: 21

I had the issue that the AfterSave event didn't fire and I found out that this happens only when the visual basic development environment is open. When I close the VBA editor, the AfterSave event fires as expected.

Upvotes: 2

wrslphil
wrslphil

Reputation: 258

Try this, think it's because you've not tested the condition of the success value. You need to check whether it's true or false

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

If Success = True Then

    Application.EnableEvents = False

    Call CopiarNovaPlanilha

    Application.EnableEvents = True

End If

End Sub

Upvotes: 1

MisterBic
MisterBic

Reputation: 307

Calling a sub directly from a workbook event sometimes causes issues. What I would recomend you to try is simply putting the content of your sub into the AfterSave event like so:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

 Application.EnableEvents = False

 ActiveWorkbook.SaveCopyAs "I:\CGP\DEOPEX\01 - Supervisão\10 - Alocação das equipes\Consulta Alocados\ALOCACAO TECNICOS.xlsx"

 Application.EnableEvents = True

End Sub

Considering you're not using any variable inside of your sub, I dont see why this wouldn't work!

Upvotes: 0

TJYen
TJYen

Reputation: 373

Place the: application.events=false in CopiarNovaPlanilha() instead:

  Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Call CopiarNovaPlanilha

End Sub

And for the module:

Sub CopiarNovaPlanilha()

    Application.EnableEvents = False

    ActiveWorkbook.SaveCopyAs "file location"
    Application.EnableEvents = True
    End Sub

Upvotes: 0

Related Questions