Excel 2003 spreadsheet error in Excel 2007

[whatsinaname]

We have an Excel 2003 spreadsheet with macros that we are trying to use in Excel 2007. Instead of the computed value being calculated for a range of cells, the cells are set to zero instead. I'm not too familiar with the macro, as I didn't write it.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/1/01 by filippoun
'
' Keyboard Shortcut: Ctrl+e
' Keyboard Shortcut: Ctrl+e THIS MACRO IS FOR THE OBJECT AND FUND TABLE
'
Calculate
Application.Goto Reference:="Criteria"
Range("V41").Select
ActiveCell.FormulaR1C1 = "'Object"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'01"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object1"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'02"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object2"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'03"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object3"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'04"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object4"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'06"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object6"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'07"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object7"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'08"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object8"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'09"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object9"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'10"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object10"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'11"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object11"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'12"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object12"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'13"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object13"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'14"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="Object14"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V41").Select
ActiveCell.FormulaR1C1 = "'Fund"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'General Funds"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="GFFunds"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'Special Funds"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="SFFunds"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'Federal Funds"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="FFFunds"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'Reimb. Funds"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="RFFunds"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="Criteria"
Range("V42").Select
ActiveCell.FormulaR1C1 = "'Total Funds"
Range("OutputA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("V41:V42"), CopyToRange:=Range("OutputB"), Unique:=False
Range("OUTPUT1").Select
Selection.Copy
Application.Goto Reference:="TFFunds"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="R1C1"
Calculate




End Sub

 

[SkipVought]

Exactly WHAT cells are being assigned ZERO?

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[combo]

Your code is recorded sequence of filter actions. You set cfiteria for column 'Object' (01, 02...), filter 'OutputA' range and copy results in named drstination range (OutputB). Next 'Output1' is copied to various destinations. Even with limited knowledge of vba you will guess what your code is doing.
This makes sense if OutputB and Output1 are linked. If you get empty filter output, you will get zeros in Output1.
Test all manually, for sure you will find the solution.


combo

 

[whatsinaname]

Skip, cells F7-20,G7-20,H7-20,I7-20,J7-20,K7-20 and L7-20 are being assigned to zero.
Combo, I'll step through the code again and try and see exactly what's going on. It's weird that it works in Excel 2003, but not in Excel 2007.

Thanks for the help, I'll let yo know what I find out.

 

[combo]

Does it work with the same data?

combo

 

[whatsinaname]

Yes, in Excel 2003 it works perfectly, but the same data in Excel 2007 loads the cells with zeros.

 

[combo]

We still do not know how your data looks like. What I could suggest:
- print your macro,
- do manually what is coded, it is not necessary to go with all sequences (it should be simple, select range, set criteria, apply autofilter etc.). Observe output,
- check calculation settings (manual/automatic, in case of formulas it can be important),
- check formatting (data vs. criteria), your code enters '01' and the filter can react differently for string and digit,
- debug your macro (run with 'F8' key, 'step into' option), after each line observe the spreadsheet data. Do the same action with the same data in 2003 and 2007 versions.


combo

 

[whatsinaname]

Thanks Combo, will do.