Thread: Macro works correctly on Excel 2010, but not on 2007...why?

Hi,

The Macro below is used to combine a number of different elements from a number of different workbooks - it works fine on Excel 2010, but comes up with #VALUE in one of the columns (column K, Sales Orders Open) when run in 2007...

Can anyone see why? It almost seems like 2007 isn't opening / activating a different sheet quickly enough...

Sub Auto_Open()
'
' Auto_Open Macro
'

'
    
        Workbooks.OpenText Filename:= _
        "h:\website\Delivery Notes Closed.txt", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

       If IsEmpty(Range("S2")) Then
      Range("S2").Value = "01.01.11"
      End If

Range("S2").Select
Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
               SearchDirection:=xlPrevious, _
               LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
               SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
    Columns("S:S").Select
    Selection.Copy
    Columns("G:G").Select
    ActiveSheet.Paste
        ActiveWorkbook.Save
    ActiveWorkbook.Close savechanges:=True
   
    
    Workbooks.OpenText Filename:= _
        "h:\website\Delivery Notes Closed.txt", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Workbooks.OpenText Filename:= _
        "h:\website\Delivery Notes Open.txt", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Workbooks.OpenText Filename:= _
        "h:\website\Sales Orders Closed.txt", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Workbooks.OpenText Filename:= _
        "h:\website\Sales Orders Open.txt", Origin _
        :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Windows("Delivery Notes Closed.txt").Activate
    
    Range("A2").Select
    Range("A2:S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Delivery Notes Open.txt").Activate
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    
        Range("K2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[1],FIND(""Sales Orders"",RC[1])+13,7)"
    anchor_cell = "A2"
curr_row = Range(anchor_cell).Row
curr_col = Range(anchor_cell).Column
last_row = Range(anchor_cell).End(xlDown).Row
last_col = Range(anchor_cell).Offset(0, 1).End(xlToRight).Column

Range(Cells(curr_row, curr_col + 10), Cells(curr_row, curr_col + 10)).Copy
Range(Cells(curr_row + 1, curr_col + 10), Cells(last_row, curr_col + 10)).PasteSpecial xlPasteFormulas
       
    ActiveWorkbook.Save
    ActiveWorkbook.Close savechanges:=True
    Windows("Delivery Notes Closed.txt").Activate
    ActiveWorkbook.Close savechanges:=False
    Windows("Sales Orders Closed.txt").Activate
    Range("A2").Select
    Range("A2:O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Sales Orders Open.txt").Activate
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    ActiveWorkbook.Save
    ActiveWorkbook.Close savechanges:=True
    Windows("Sales Orders Closed.txt").Activate
    ActiveWorkbook.Close savechanges:=False

    
   Workbooks.OpenText Filename:= _
        "h:\website\Returns.txt", Origin _
        :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""Deliveries"",RC[-1])+11,7)"
    anchor_cell = "D2"

curr_row = Range(anchor_cell).Row
curr_col = Range(anchor_cell).Column
last_row = Range(anchor_cell).End(xlDown).Row
last_col = Range(anchor_cell).Offset(0, 1).End(xlToRight).Column

Range(Cells(curr_row, curr_col + 1), Cells(curr_row, last_col)).Copy
Range(Cells(curr_row + 1, curr_col + 1), Cells(last_row, curr_col + 1)).PasteSpecial xlPasteFormulas
        ActiveWorkbook.Save
    ActiveWorkbook.Close savechanges:=True

       Windows("Website_Macro.xlsm").Activate
    ActiveWorkbook.Close savechanges:=False
End Sub

Cheers,
Matt

Have you tried stepping through the code to see what is happening?

Note: you must use code tags when posting code on the forum. I have added them for you as it's your first post - welcome, by the way - but please learn to use them in future.