auto_open macro not performing as expected

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
Auto_Open 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?

Eric

put a msgbox at the start of the code. Save the workbook and close it. Now
open the workbook again to activate the Auto_Open 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


"Eric Winegarner" <Eric Winegarner@discussions.microsoft.com> wrote in
message news:1844FF51-1187-4B0B-B5B0-F67B8D6B01AD@microsoft.com...
>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
> Auto_Open 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?