auto_open macro not performing as expected

[Eric Winegarner]

I have the following code as an auto_open macro in workbook A:

Sub auto_open()

Dim retry_master, retry_cdc As Workbook
Dim cdc, bookname As String

cdc = Worksheets("Sheet1").Range("E5").Value

Workbooks.OpenText Filename:="O:\Retry_Reports\retry_report_" & cdc &
".rep", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20,
1)), _
TrailingMinusNumbers:=True

bookname = Workbooks("retry_report_" & cdc & ".rep").Name

For Each cell In Range("A1:A1000")
If cell.Value = "End" Then
totalre = cell.Offset(0, 5).Value
withre = cell.Offset(1, 5).Value
withoutre = cell.Offset(2, 5).Value
scanned = cell.Offset(3, 4).Value
End If
Next cell

Workbooks(bookname).Close

Workbooks.Open ("O:\Operations\Retry Report " & Year(Now - 1) & ".xls")

Set retry_master = Workbooks("Retry Report " & Year(Now - 1) & ".xls")

month1 = Month(Now - 1)

Select Case month1
Case 1
month2 = "January"
Case 2
month2 = "February"
Case 3
month2 = "March"
Case 4
month2 = "April"
Case 5
month2 = "May"
Case 6
month2 = "June"
Case 7
month2 = "July"
Case 8
month2 = "August"
Case 9
month2 = "September"
Case 10
month2 = "October"
Case 11
month2 = "November"
Case 12
month2 = "December"
End Select

For Each cell In retry_master.Worksheets("Sheet1").Range("A1:A1000")
If cell.Value = month2 Then
For Each cell1 In Range(cell.Offset(0, 1).Address,
Range(cell.Offset(0, 40).Address))
If Day(cell1.Value) = Day(Now - 1) And Month(cell1.Value) =
Month(Now - 1) And Year(cell1.Value) = Year(Now - 1) Then
cell1.Offset(1, 0).Value = totalre
cell1.Offset(2, 0).Value = withre
cell1.Offset(3, 0).Value = withoutre
cell1.Offset(5, 0).Value = scanned
End If
Next cell1
End If
Next cell

retry_master.Close savechanges:=True


End Sub

I basically just retrieves data from workbook B and writes it to workbook C.
When I open workbook A, the code runs without error. It even gathers the
data from workbook B. THe problem is that when I open workbook A and let the
Autpen code do its thing, it doesn't write the dat to workbook C.
However, if I open workbook A and don't allow the code to auto run, and then
run the code from within the Visual Basic Editor, or step through it,
everything in the code is accomplished without error. It's the wierdest
thing that I've come accross. Can any one help?

 

[Trevor Shuttleworth]

Eric

put a msgbox at the start of the code. Save the workbook and close it. Now
open the workbook again to activate the Autpen code. When the message is
displayed, press Ctrl-Break and select debug. Now step through the code
checking which workbook is active at what stage and the values of the
variables.

Regards

Trevor