I am failing to find how to make this new macro run smoothly in other worksheets. The code fails when I copy down the clean formula to the bottom of the column (underlined and in Italic below). I have gone through the forum and sorry, but I am not seeing a solution to work. Thanks for any clues you can send my way.
Sub Clean_Step1_10Col()
'
' Clean_Step1_10Col Macro
' Filter out Blanks ColA Create Col Insert Formula Copy Down
'
'
Cells.Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$AD$7169").AutoFilter Field:=1, Criteria1:="<>"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=2
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("D1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("H1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("J1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("L1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("N1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("P1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("R1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("T1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
End Sub
Sub Clean_Step2_10Col()
'
' Clean_Step2_10Col Macro
' Copy/Paste/Delete CleanFormula Field Remove Filter
'
'
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.AutoFilter
End Sub
Sub Clean_Step1_10Col()
'
' Clean_Step1_10Col Macro
' Filter out Blanks ColA Create Col Insert Formula Copy Down
'
'
Cells.Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$AD$7169").AutoFilter Field:=1, Criteria1:="<>"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=2
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("D1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("H1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("J1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("L1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("N1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("P1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("R1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("T1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
End Sub
Sub Clean_Step2_10Col()
'
' Clean_Step2_10Col Macro
' Copy/Paste/Delete CleanFormula Field Remove Filter
'
'
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.AutoFilter
End Sub