Hi Murray,
Thank you so much! It almost worked lol.
Well, on the first tab it works, however i have several tabs (i should have mentioned that)
which i need to apply this exact same logic to.
So yes it works correctly on the first tab however when you get to the second, third, and further tabs it seems to be referencing the value we are storing as "MySelection".
Here's the code (Tab 1 & Tab 2 are labeled)
Sub MacroFPAOEEEXP()
ActiveWorkbook.Names.Add Name:="MySelection", RefersTo:=Selection
'
' MacroFPAOEEEXP Macro
'
Sheets("OEENEW").Select
Range("A3:X3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows("OEE NEW").Activate
Range("A33:X33").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FPA").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Windows("OEE NEW").Activate
ChDir "C:\Users\FINANCE\FPA\OE&E"
ActiveWorkbook.Save
ActiveWindow.Close
'(TAB 1 PASTE MONTHLY ACTUALS - JO OTHER)
Windows("FPA").Activate
Sheets("JO OTHER").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""JUDGES"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'highlight duplicates
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
.FormatConditions(2).StopIfTrue = False
End With
Range("H5").FormulaArray = "=IF(SUM(1/COUNTIF(MySelection,MySelection))-(COUNTIF(MySelection,0)>0)-COUNTIF(MySelection,"">0"")<>0,""Duplicates"",""No duplicates"")"
Range("H5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'(TAB 2 PASTE MONTHLY ACTUALS - EXEC)
Sheets("EXEC").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""EXECUTIVE"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'highlight duplicates
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
.FormatConditions(2).StopIfTrue = False
End With
Range("H5").FormulaArray = "=IF(SUM(1/COUNTIF(MySelection,MySelection))-(COUNTIF(MySelection,0)>0)-COUNTIF(MySelection,"">0"")<>0,""Duplicates"",""No duplicates"")"
Range("H5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.CutCopyMode = False