Code works in Excel Modules but not in ThisWorkbook module.

Question

I have a very simple piece of VBA code to copy the value of a cell, open another workbook, and paste that cell value into the newly opened workbook. It works perfectly in a normal module. But when I run the code in the ThisWorkbook module, for the purpose of triggering it when the file is saved, the other workbook will not open. Simpler macros like opening a MsgBox work fine in ThisWorkbook, however. Another weird thing is that the code DOES work in ThisWorkbook if I am viewing the code. Anyone know why this might be happening? I'm running Excel 2010.

Code is below:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rangetocopy As String
Dim w1 As Workbook
Dim w2 As Workbook

Set w1 = ActiveWorkbook
Calculate

rangetocopy = w1.Sheets("Reference Data").Range("F10").Value

Set w2 = Workbooks.Open("C:\Users\user1\Documents\To-Do.xlsm")

w2.Worksheets("Masterlist").Activate
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Value = rangetocopy

w1.Worksheets("To-do List").Activate

End Sub

Any thoughts?

Call the sub routine from the module when the events triggers. — Ryan Wildry
If you place a breakpoint (F9) on the Set w1 = ActiveWorkbook line, and then hit Ctrl+S... does the breakpoint get hit? FWIW working with Select and Activate is far from ideal; consider figuring out other ways to do your thing. — Mathieu Guindon
Set w1 = ActiveWorkbook should probably be Set w1 = ThisWorkbook Are you seeing any error when the workbook doesn't get opened? — Tim Williams
If the workbook fails to open then that should raise an error: the code should not then fail instead on the next line. Maybe the workbook did open and you're not seeing it (is it hidden?) - does it appear in the project tree in the VB editor? BTW I will get a notification if you include @timwilliams in your comment... — Tim Williams
I can't explain that: if the workbook opens then it should be there in the tree - if it doesn't open then that should have raised an error. Try adding MsgBox w2.Sheets.Count directly after the Open line: what do you see when it runs? — Tim Williams

user1644040 user1644040 · Accepted Answer · 2016-08-26T18:53:19

The problem was caused by a 3rd party add-in called WorkSite. Once I disconnected from that, the macro worked fine.

Code works in Excel Modules but not in ThisWorkbook module.

1 Answers