How to edit Macro to ensure print area and streamline

M

MacroJoe

New Member
Joined
Oct 30, 2018
Messages
1
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
209
Coco1
C
P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
105
willow1985
willow1985
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
308
Azamamir
A

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top