The macro is this. If there is a better way to post this in here please let me know. Thanks. Oh BTW, all of the columns will remain the same and don't need any adjustment, just the # ranges are what may change week to week.
Sub FullPL41Update()
'
' FullPL41Update Macro
' Full Update
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C1,D:D,E:E,F:F,G:G,H:H,I:I,J:J,L:L").Select
Range("L1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("C1,D:D,E:E,F:F,G:G,H:H,I:I,J:J,L:L,N:N,O:O,Q:Q,S:S,T:T,U:U,V:V,W:W"). _
Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R3").Select
Sheets("Formula").Select
Range("C8").Select
Selection.Copy
Sheets("Price List 041").Select
ActiveSheet.Paste
Range("R3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R2C1:R21C2, 2, FALSE)"
Range("R3").Select
Selection.AutoFill Destination:=Range("R3:R19662")
Range("R3:R19662").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"R3:R19662"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A3:R19662")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A19633").Select
Selection.End(xlUp).Select
Rows("22:22").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R23").Select
Sheets("Formula").Select
Range("C9").Select
Selection.Copy
Sheets("Price List 041").Select
ActiveSheet.Paste
Range("R23").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R22C1:R112C2, 2, FALSE)"
Range("R23").Select
Selection.AutoFill Destination:=Range("R23:R19663")
Range("R23:R19663").Select
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"R23:R19663"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A23:R19663")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A19636").Select
Selection.End(xlUp).Select
Range("A20").Select
ActiveWindow.SmallScroll Down:=51
Rows("104:104").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=9
Range("A106").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"P106:P19665"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A106:R19665")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-27
Range("A19609").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=279
Range("P408").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Rows("1095:1095").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1096").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"M1096:M19666"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A1096:R19666")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M19630").Select
Selection.End(xlUp).Select
Rows("1271:1271").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1272").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"K1272:K19667"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A1272:R19667")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("K19642").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-81
Range("K15016").Select
Selection.End(xlUp).Select
Rows("1296:1296").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-3
End Sub