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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You must log in or register to reply here.

Similar threads

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
277
Peter_SSs
Peter_SSs
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
104
Northern NY Design
N
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
R
  • Solved
Retype Macro to run properly
2
Replies
11
Views
88
Alex Blakenburg
A

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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