Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto_Open Macro | Excel Discussion (Misc queries) | |||
Force read-only in auto_open macro | Excel Discussion (Misc queries) | |||
Help with SaveAs Macro on Auto_Open | Excel Programming | |||
Auto Open macro not performing as expected | Excel Programming | |||
Auto_Open vs. macro | Excel Programming |