I have been growing more and more comfortable with Excel in my new position and really enjoy learning how to work more efficiently with it. Recently I have been filling in for someone on long term disability and I have to extract data from AVANTIS to an existing Macro every week that we use to keep track of open work orders. The issue I have with it right now is that I always have to go through and CTRL+SHIFT+8 and then Set print area for each tab on my workbook. I also have to remove duplicates by two selections from the first data before I put it through the macro. I just wanted to try to figure out a way to add this to the existing macro and also hope to learn more about macros in doing so. I will upload the template here if it will let me. Thanks for your help!
Current code
Sub nonoutage()
'
' nonoutage Macro
' Macro recorded 10/3/2006 by smr00smr972276
'
'
Range("A:A,N:N").Select
Range("N1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.NumberFormat = "m/d/yy;@"
Sheets("Total Backlog").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Total Backlog").Select
Application.Goto Reference:="R1C1:R3000C26"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A4").Select
Selection.autofilter Field:=11, Criteria1:="=*1-el*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Elec").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("G39").Select
Selection.autofilter Field:=11, Criteria1:="=*1-op*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Oper").Select
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J138").Select
Selection.autofilter Field:=11, Criteria1:="=*matl*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Matl Hndlg").Select
ActiveWindow.SmallScroll Down:=-34
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("I67").Select
Selection.autofilter Field:=11, Criteria1:="1-MAINT HVAC"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("HVAC").Select
ActiveWindow.SmallScroll Down:=-48
Cells.Select
Range("C1").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.Select
Range("C1").Activate
ActiveWindow.SmallScroll ToRight:=-2
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J76").Select
Selection.autofilter Field:=11, Criteria1:="=*1-in*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Inst").Select
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Matl Hndlg").Select
Cells.Select
Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("Total Backlog").Select
Range("I89").Select
Selection.autofilter Field:=11, Criteria1:="=*1-mai*", Operator:=xlAnd, _
Criteria2:="<>*hvac*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Mech Maint").Select
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("G2").Select
Selection.autofilter Field:=11, Criteria1:="=*2-*", Operator:=xlAnd, _
Criteria2:="<>*rick*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Contr").Select
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Rows("1:1").Select
Selection.autofilter
Sheets("Total Backlog").Select
Range("K47").Select
Selection.autofilter Field:=11, Criteria1:="1-WAREHOUSE"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("WH").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Selection.autofilter Field:=11, Criteria1:="=*RESC*", Operator:=xlOr, _
Criteria2:="=*SAFE*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Resc-Safety").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Selection.autofilter Field:=11, Criteria1:="1-LAB (DEPT)"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Lab").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("K215").Select
Selection.autofilter Field:=11, Criteria1:="1-ENGINEERING"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Eng").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J138").Select
Selection.autofilter Field:=11, Criteria1:="=1-aeci*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("AECI-Union-NonUnion").Select
ActiveWindow.SmallScroll Down:=-34
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Elec").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Oper").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Matl Hndlg").Select
ActiveWindow.SmallScroll Down:=-49
Rows("1:1").Select
Selection.autofilter
Sheets("HVAC").Select
ActiveWindow.SmallScroll Down:=-28
Rows("1:1").Select
Selection.autofilter
Sheets("Inst").Select
ActiveWindow.SmallScroll Down:=-192
Rows("1:1").Select
Selection.autofilter
Sheets("Mech Maint").Select
Range("A532").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("Contr").Select
Range("D130").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("WH").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Resc-Safety").Select
ActiveWindow.SmallScroll Down:=-7
Rows("1:1").Select
Selection.autofilter
Sheets("Lab").Select
Range("E69").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("Eng").Select
Range("J27").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("AECI-Union-NonUnion").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Total Backlog").Select
Range("A1").Select
For Each Wksht In Worksheets
Wksht.PageSetup.RightFooter = Format(Now, "MMM DD, YYYY")
Next Wksht
Sheets("Total Backlog").Select
Columns("L").NumberFormat = "###0.00"
Range("K5").Select
ActiveSheet.ShowAllData
Sheets("Sheet1").Select
Range("B1").Select
ActiveWindow.SelectedSheets.Delete
End Sub
Current code
Sub nonoutage()
'
' nonoutage Macro
' Macro recorded 10/3/2006 by smr00smr972276
'
'
Range("A:A,N:N").Select
Range("N1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.NumberFormat = "m/d/yy;@"
Sheets("Total Backlog").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Total Backlog").Select
Application.Goto Reference:="R1C1:R3000C26"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A4").Select
Selection.autofilter Field:=11, Criteria1:="=*1-el*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Elec").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("G39").Select
Selection.autofilter Field:=11, Criteria1:="=*1-op*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Oper").Select
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J138").Select
Selection.autofilter Field:=11, Criteria1:="=*matl*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Matl Hndlg").Select
ActiveWindow.SmallScroll Down:=-34
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("I67").Select
Selection.autofilter Field:=11, Criteria1:="1-MAINT HVAC"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("HVAC").Select
ActiveWindow.SmallScroll Down:=-48
Cells.Select
Range("C1").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.Select
Range("C1").Activate
ActiveWindow.SmallScroll ToRight:=-2
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J76").Select
Selection.autofilter Field:=11, Criteria1:="=*1-in*", Operator:=xlAnd
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Inst").Select
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Matl Hndlg").Select
Cells.Select
Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("Total Backlog").Select
Range("I89").Select
Selection.autofilter Field:=11, Criteria1:="=*1-mai*", Operator:=xlAnd, _
Criteria2:="<>*hvac*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Mech Maint").Select
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("G2").Select
Selection.autofilter Field:=11, Criteria1:="=*2-*", Operator:=xlAnd, _
Criteria2:="<>*rick*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Contr").Select
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Rows("1:1").Select
Selection.autofilter
Sheets("Total Backlog").Select
Range("K47").Select
Selection.autofilter Field:=11, Criteria1:="1-WAREHOUSE"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("WH").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Selection.autofilter Field:=11, Criteria1:="=*RESC*", Operator:=xlOr, _
Criteria2:="=*SAFE*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Resc-Safety").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Selection.autofilter Field:=11, Criteria1:="1-LAB (DEPT)"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Lab").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("K215").Select
Selection.autofilter Field:=11, Criteria1:="1-ENGINEERING"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("Eng").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Total Backlog").Select
Range("J138").Select
Selection.autofilter Field:=11, Criteria1:="=1-aeci*"
Application.Goto Reference:="R1C1:R3000C26"
Selection.Copy
Sheets("AECI-Union-NonUnion").Select
ActiveWindow.SmallScroll Down:=-34
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.autofilter
Sheets("Elec").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Oper").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Matl Hndlg").Select
ActiveWindow.SmallScroll Down:=-49
Rows("1:1").Select
Selection.autofilter
Sheets("HVAC").Select
ActiveWindow.SmallScroll Down:=-28
Rows("1:1").Select
Selection.autofilter
Sheets("Inst").Select
ActiveWindow.SmallScroll Down:=-192
Rows("1:1").Select
Selection.autofilter
Sheets("Mech Maint").Select
Range("A532").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("Contr").Select
Range("D130").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("WH").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Resc-Safety").Select
ActiveWindow.SmallScroll Down:=-7
Rows("1:1").Select
Selection.autofilter
Sheets("Lab").Select
Range("E69").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("Eng").Select
Range("J27").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.autofilter
Sheets("AECI-Union-NonUnion").Select
Rows("1:1").Select
Selection.autofilter
Sheets("Total Backlog").Select
Range("A1").Select
For Each Wksht In Worksheets
Wksht.PageSetup.RightFooter = Format(Now, "MMM DD, YYYY")
Next Wksht
Sheets("Total Backlog").Select
Columns("L").NumberFormat = "###0.00"
Range("K5").Select
ActiveSheet.ShowAllData
Sheets("Sheet1").Select
Range("B1").Select
ActiveWindow.SelectedSheets.Delete
End Sub
