My one week effort code, can you check it up ?

A

AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
Hey there :)

I have been busting my A** out in the last week to generate one consolidated report from two different reports

i know it may look simple but i just learned vba one week ago haha....

anyhow, first i would like to thank everyone here for their support.

second, can you please inspect the code and tell me what can i improve ?

i really appreciate your support guys.


Code: 
[COLOR=#574123]Sub SREPORT()[/COLOR]

[COLOR=#574123]'[/COLOR]
[COLOR=#574123]' Macro19 Macro[/COLOR]
[COLOR=#574123]'[/COLOR]
[COLOR=#574123]'Application.Calculation = xlCalculationManual[/COLOR]

[COLOR=#574123]'Reformatting all cells to general to avoid misformatted columns[/COLOR]
[COLOR=#574123]Range("A6:DD104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "General"[/COLOR]

[COLOR=#574123]'clear existing data[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("A6:DD104856").ClearContents[/COLOR]
[COLOR=#574123]' FYI (& lDestLastRow) was with the above range .[/COLOR]

[COLOR=#574123]'open PR Distribution workbook obtained from Oracle[/COLOR]
[COLOR=#574123]Workbooks.Open "C:\Users\Real_\OneDrive - Saudi Air Navigation Services\SSD Work\Budget & P2P\P2P Assessment and improvement\ERP Data Dump\PRs Encumbrance seceret report\Report Generation\Resources\Updated (PR Distribution).xls"[/COLOR]

[COLOR=#574123]'Copy range to clipboard[/COLOR]
[COLOR=#574123]Workbooks("Updated (PR Distribution).xls").Worksheets("Updated (PR Distribution)").Range("A1:AK104856").Copy[/COLOR]

[COLOR=#574123]'PasteSpecial to paste values, formulas, formats, etc.[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("A6").PasteSpecial Paste:=xlPasteValues[/COLOR]

[COLOR=#574123]'disable copy to clipboard notification[/COLOR]
[COLOR=#574123]'Application.DisplayAlerts = False[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]

[COLOR=#574123]'Close Report[/COLOR]
[COLOR=#574123]Workbooks("Updated (PR Distribution).xls").Close SaveChanges:=True[/COLOR]

[COLOR=#574123]'Enable notifications[/COLOR]
[COLOR=#574123]'Application.DisplayAlerts = True[/COLOR]
[COLOR=#574123]Application.CutCopyMode = True[/COLOR]


[COLOR=#574123]'Phase2()[/COLOR]
[COLOR=#574123]'[/COLOR]
[COLOR=#574123]' REWORKING_SECOND Macro[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]'FilterUnfilter[/COLOR]
[COLOR=#574123]Rows("6:6").Select[/COLOR]
[COLOR=#574123]Selection.AutoFilter[/COLOR]
[COLOR=#574123]Selection.AutoFilter[/COLOR]
[COLOR=#574123]'Inserting a Column at Column C to add activity Code[/COLOR]
[COLOR=#574123]Range("C1").EntireColumn.Insert[/COLOR]

[COLOR=#574123]Range("C6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Activity Code"[/COLOR]
[COLOR=#574123]Columns("C:C").EntireColumn.AutoFit[/COLOR]



[COLOR=#574123]Range("C7").Activate[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "=MID(RC[23],33,4)"[/COLOR]
[COLOR=#574123]Range("C7").Copy[/COLOR]
[COLOR=#574123]Range("C8:C" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial[/COLOR]

[COLOR=#574123]Range("C7").Activate[/COLOR]


[COLOR=#574123]Dim rCell As Range[/COLOR]
[COLOR=#574123]Dim rRng As Range[/COLOR]

[COLOR=#574123]Set rRng = Range(Range("C7"), Range("C7").End(xlDown))[/COLOR]

[COLOR=#574123]For Each rCell In rRng.Cells[/COLOR]
[COLOR=#574123]rCell.Select[/COLOR]
[COLOR=#574123]rCell.Activate[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "=MID(RC[23],33,4)"[/COLOR]
[COLOR=#574123]Next rCell[/COLOR]


[COLOR=#574123]'Format Dates ITS IMPORTANT CUZ IF ITS NOT THERE THE ADVANCED FILTER WILL NOT WORK, MAYBE TOO THE ACTIVITY CODE NUMBER CHECK IT![/COLOR]
[COLOR=#574123]Range(Range("D7"), Range("D7").End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]

[COLOR=#574123]Range(Range("G7"), Range("G7").End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]

[COLOR=#574123]Range(Range("AH7"), Range("AH7").End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]


[COLOR=#574123]Worksheets("Advanced Filter").Activate[/COLOR]

[COLOR=#574123]Range(Range("D11"), Range("D11").End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "@"[/COLOR]

[COLOR=#574123]Worksheets("Raw Data for PR Dist").Activate[/COLOR]

[COLOR=#574123]'First Advanced Filter[/COLOR]
[COLOR=#574123]Worksheets("Raw Data for PR Dist").Range("A6:AL" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _[/COLOR]
[COLOR=#574123]:=Sheets("Advanced Filter").Range("B10:D174"), Unique:=False[/COLOR]

[COLOR=#574123]'delete PR Dist SSD Report sheet if exists[/COLOR]
[COLOR=#574123]Application.DisplayAlerts = False[/COLOR]
[COLOR=#574123]For Each Sheet In ActiveWorkbook.Worksheets[/COLOR]
[COLOR=#574123]If Sheet.Name = "PR Dist SSD Report" Then[/COLOR]


[COLOR=#574123]Sheet.Delete[/COLOR]





[COLOR=#574123]'fix source issue if sheet is deleted BOOMYAH[/COLOR]
[COLOR=#574123]' Workbooks("PRep.xlsm").Worksheets("DONT TOUCH").Activate[/COLOR]

[COLOR=#574123]' Cells.Replace What:="#REF!", Replacement:="'PR Dist SSD Report'!", LookAt _[/COLOR]
[COLOR=#574123]' :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _[/COLOR]
[COLOR=#574123]' ReplaceFormat:=False[/COLOR]



[COLOR=#574123]' Else[/COLOR]

[COLOR=#574123]'add a new sheet[/COLOR]
[COLOR=#574123]' Sheets.Add After:=ActiveSheet[/COLOR]
[COLOR=#574123]'Rename the new sheet generated[/COLOR]
[COLOR=#574123]' ActiveSheet.Name = "PR Dist SSD Report"[/COLOR]
[COLOR=#574123]' Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]' With ActiveWorkbook.Sheets("PR Dist SSD Report").Tab[/COLOR]
[COLOR=#574123]' .Color = 65535[/COLOR]
[COLOR=#574123]' .TintAndShade = 0[/COLOR]
[COLOR=#574123]' End With[/COLOR]



[COLOR=#574123]'fix source issue if sheet is deleted BOOMYAH[/COLOR]
[COLOR=#574123]' Workbooks("PRep.xlsm").Worksheets("DONT TOUCH").Activate[/COLOR]

[COLOR=#574123]' Cells.Replace What:="#REF!", Replacement:="'PR Dist SSD Report'!", LookAt _[/COLOR]
[COLOR=#574123]' :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _[/COLOR]
[COLOR=#574123]' ReplaceFormat:=False[/COLOR]



[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]Next Sheet[/COLOR]

[COLOR=#574123]'add a new sheet[/COLOR]
[COLOR=#574123]Sheets.Add After:=ActiveSheet[/COLOR]
[COLOR=#574123]'Rename the new sheet generated[/COLOR]
[COLOR=#574123]ActiveSheet.Name = "PR Dist SSD Report"[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]With ActiveWorkbook.Sheets("PR Dist SSD Report").Tab[/COLOR]
[COLOR=#574123].Color = 65535[/COLOR]
[COLOR=#574123].TintAndShade = 0[/COLOR]
[COLOR=#574123]End With[/COLOR]


[COLOR=#574123]Application.DisplayAlerts = True[/COLOR]

[COLOR=#574123]'copy first generated report[/COLOR]
[COLOR=#574123]'Copy range to clipboard[/COLOR]

[COLOR=#574123]Worksheets("Raw Data for PR Dist").Activate[/COLOR]

[COLOR=#574123]'Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]'Application.CutCopyMode = True[/COLOR]

[COLOR=#574123]Range("A6:AL" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]


[COLOR=#574123]'PasteSpecial to paste values, formulas, formats, etc.[/COLOR]

[COLOR=#574123]Worksheets("PR Dist SSD Report").Activate[/COLOR]
[COLOR=#574123]Range("A1").PasteSpecial Paste:=xlPasteValues[/COLOR]

[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Application.CutCopyMode = True[/COLOR]



[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Activate[/COLOR]

[COLOR=#574123]Worksheets("Raw Data for PR Dist").ShowAllData[/COLOR]


[COLOR=#574123]'CHECK IF SELECTION OF FILTER IS CORRECT [/COLOR]:smile:[COLOR=#574123] PLEASE DO HAHA...................................[/COLOR]
[COLOR=#574123]'\.................................................................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]
[COLOR=#574123]'........................'........................'........................[/COLOR]
[COLOR=#574123]'........................[/COLOR]



[COLOR=#574123]'START SECOND REPORT[/COLOR]
[COLOR=#574123]'Dynamic range for New PRs added[/COLOR]
[COLOR=#574123]'second filter FINALLY GOT IT[/COLOR]
[COLOR=#574123]Worksheets("Raw Data for PR Dist").Range("A6:AL" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter _[/COLOR]
[COLOR=#574123]Action:=xlFilterInPlace, _[/COLOR]
[COLOR=#574123]CriteriaRange:=Sheets("PR Dist SSD Report").Range("A1", Sheets("PR Dist SSD Report").Range("A" & Rows.Count).End(xlUp)), _[/COLOR]
[COLOR=#574123]Unique:=False[/COLOR]


[COLOR=#574123]'clear existing data[/COLOR]
[COLOR=#574123]'Workbooks("PRep.xlsm").Worksheets("PR Dist SSD Report").Range("A1:DD104856").ClearContents[/COLOR]
[COLOR=#574123]' FYI (& lDestLastRow) was with the above range .[/COLOR]



[COLOR=#574123]'copy second generated report[/COLOR]
[COLOR=#574123]'Copy range to clipboard[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("A6:AL" & Range("A" & Rows.Count).End(xlUp).Row).Copy[/COLOR]

[COLOR=#574123]'PasteSpecial to paste values, formulas, formats, etc.[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("PR Dist SSD Report").Range("A1").PasteSpecial Paste:=xlPasteValues[/COLOR]


[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Application.CutCopyMode = True[/COLOR]

[COLOR=#574123]'IN THE END[/COLOR]
[COLOR=#574123]'Deleting a Column at Column C to Exclude activity Code added in the begining[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("C1").EntireColumn.Delete[/COLOR]

[COLOR=#574123]'Filter/unfilter[/COLOR]
[COLOR=#574123]Worksheets("Raw Data for PR Dist").ShowAllData[/COLOR]


[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("PR Dist SSD Report").Activate[/COLOR]


[COLOR=#574123]'fix new report[/COLOR]
[COLOR=#574123]Cells.Select[/COLOR]
[COLOR=#574123]Cells.EntireColumn.AutoFit[/COLOR]
[COLOR=#574123]Range("A1").Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlToRight)).Select[/COLOR]
[COLOR=#574123]With Selection.Interior[/COLOR]
[COLOR=#574123].Pattern = xlSolid[/COLOR]
[COLOR=#574123].PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#574123].ThemeColor = xlThemeColorAccent1[/COLOR]
[COLOR=#574123].TintAndShade = -0.249977111117893[/COLOR]
[COLOR=#574123].PatternTintAndShade = 0[/COLOR]
[COLOR=#574123]End With[/COLOR]
[COLOR=#574123]With Selection.Font[/COLOR]
[COLOR=#574123].ThemeColor = xlThemeColorDark1[/COLOR]
[COLOR=#574123].TintAndShade = 0[/COLOR]
[COLOR=#574123]End With[/COLOR]
[COLOR=#574123]Selection.Font.Bold = True[/COLOR]
[COLOR=#574123]Selection.Font.Underline = xlUnderlineStyleSingle[/COLOR]
[COLOR=#574123]Selection.AutoFilter[/COLOR]
[COLOR=#574123]Range("A1").Select[/COLOR]

[COLOR=#574123]'----------------------------------------------[/COLOR]
[COLOR=#574123]'FIRST fixing DATE in PR DIST REPORT[/COLOR]

[COLOR=#574123]'Inserting a Column at Create Date and Time[/COLOR]
[COLOR=#574123]Range("E1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("E1").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "GL Time"[/COLOR]




[COLOR=#574123]'tofix date issue must use this[/COLOR]

[COLOR=#574123]Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]




[COLOR=#574123]Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]

[COLOR=#574123]Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]

[COLOR=#574123]Range("E1").EntireColumn.Delete[/COLOR]

[COLOR=#574123]'------------------------------------------[/COLOR]

[COLOR=#574123]'Rearrange dates[/COLOR]
[COLOR=#574123]'Columns("D:D").Select[/COLOR]
[COLOR=#574123]'Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _[/COLOR]
[COLOR=#574123]' TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _[/COLOR]
[COLOR=#574123]' Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _[/COLOR]
[COLOR=#574123]' :=Array(1, 4), TrailingMinusNumbers:=True[/COLOR]


[COLOR=#574123]'----------------------------------------------[/COLOR]
[COLOR=#574123]'Second fixing DATE in PR DIST REPORT[/COLOR]

[COLOR=#574123]'Inserting a Column at Create Date and Time[/COLOR]
[COLOR=#574123]Range("H1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("H1").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "GL Cancelled Time"[/COLOR]




[COLOR=#574123]'tofix date issue must use this[/COLOR]

[COLOR=#574123]Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]




[COLOR=#574123]Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.TextToColumns Destination:=Range("G2"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]

[COLOR=#574123]Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]

[COLOR=#574123]Range("H1").EntireColumn.Delete[/COLOR]

[COLOR=#574123]'------------------------------------------[/COLOR]




[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Activate[/COLOR]



[COLOR=#574123]'Reformatting all cells to general to avoid misformatted columns[/COLOR]
[COLOR=#574123]' Range("A6:DD104856").Select[/COLOR]
[COLOR=#574123]' Selection.NumberFormat = "General"[/COLOR]



[COLOR=#574123]'clear existing data[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("A6:DD104856").ClearContents[/COLOR]
[COLOR=#574123]' FYI (& lDestLastRow) was with the above range .[/COLOR]

[COLOR=#574123]'open PR Lines workbook obtained from Oracle[/COLOR]
[COLOR=#574123]Workbooks.Open "C:\Users\Real_\OneDrive - Saudi Air Navigation Services\SSD Work\Budget & P2P\P2P Assessment and improvement\ERP Data Dump\PRs Encumbrance seceret report\Report Generation\Resources\Updated (PR Lines).xls"[/COLOR]


[COLOR=#574123]'istoppedhere[/COLOR]


[COLOR=#574123]'Copy range to clipboard[/COLOR]
[COLOR=#574123]Workbooks("Updated (PR Lines).xls").Worksheets("Updated (PR Lines)").Range("A1:BD104856").Copy[/COLOR]

[COLOR=#574123]'PasteSpecial to paste values, formulas, formats, etc.[/COLOR]
[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Range("A6").PasteSpecial Paste:=xlPasteValues[/COLOR]





[COLOR=#574123]'disable copy to clipboard notification[/COLOR]
[COLOR=#574123]'Application.DisplayAlerts = False[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]

[COLOR=#574123]'Close Report[/COLOR]
[COLOR=#574123]Workbooks("Updated (PR Lines).xls").Close SaveChanges:=True[/COLOR]

[COLOR=#574123]'Enable notifications[/COLOR]
[COLOR=#574123]'Application.DisplayAlerts = True[/COLOR]
[COLOR=#574123]Application.CutCopyMode = True[/COLOR]


[COLOR=#574123]'Phase2()[/COLOR]

[COLOR=#574123]'Inserting a Column at Column D to Seperate Creation Time from Date and orgnize Date[/COLOR]

[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("Raw Data for PR Dist").Activate[/COLOR]

[COLOR=#574123]Range("D1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("D6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Created Time"[/COLOR]

[COLOR=#574123]'tofix date issue must use this[/COLOR]
[COLOR=#574123]Range("C7").Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]



[COLOR=#574123]Range("C7").Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.TextToColumns Destination:=Range("C7"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]



[COLOR=#574123]Range("D7:D104856").Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]



[COLOR=#574123]'Inserting a Column at Column D to Seperate Cancelled Time from Date and orgnize Date[/COLOR]
[COLOR=#574123]Range("K1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("K6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Cancel Time"[/COLOR]

[COLOR=#574123]'tofix date issue must use this[/COLOR]
[COLOR=#574123]Range("J7:J104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]


[COLOR=#574123]Range("J7:J104856").Select[/COLOR]
[COLOR=#574123]Selection.TextToColumns Destination:=Range("J7"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]


[COLOR=#574123]Range("K7:K104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]



[COLOR=#574123]'Inserting a Column for Closed Date and time[/COLOR]
[COLOR=#574123]Range("N1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("N6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Closed Time"[/COLOR]

[COLOR=#574123]'tofix date issue must use this[/COLOR]
[COLOR=#574123]Range("M7:M104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]


[COLOR=#574123]Range("M7:M104856").Select[/COLOR]


[COLOR=#574123]Selection.TextToColumns Destination:=Range("M7"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]


[COLOR=#574123]Range("N7:N104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]


[COLOR=#574123]'Inserting a Column for Need By Date and time AC then AD[/COLOR]

[COLOR=#574123]Range("AD1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("AD6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Need-By Time"[/COLOR]

[COLOR=#574123]'tofix date issue must use this[/COLOR]
[COLOR=#574123]Range("AC7:AC104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]


[COLOR=#574123]Range("AC7:AC104856").Select[/COLOR]

[COLOR=#574123]Selection.TextToColumns Destination:=Range("AC7"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]


[COLOR=#574123]Range("AD7:AD104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]


[COLOR=#574123]'Inserting a Column at Rate Date and Time[/COLOR]
[COLOR=#574123]Range("BD1").EntireColumn.Insert[/COLOR]
[COLOR=#574123]Range("BD6").Select[/COLOR]
[COLOR=#574123]ActiveCell.FormulaR1C1 = "Rate Time"[/COLOR]

[COLOR=#574123]'tofix date issue must use this[/COLOR]
[COLOR=#574123]Range("BC7:BC104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"[/COLOR]


[COLOR=#574123]Range("BC7:BC104856").Select[/COLOR]

[COLOR=#574123]Selection.TextToColumns Destination:=Range("BC7"), DataType:=xlFixedWidth, _[/COLOR]
[COLOR=#574123]FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-en-US]mmmm d, yyyy;@"[/COLOR]


[COLOR=#574123]Range("BD7:BD104856").Select[/COLOR]
[COLOR=#574123]Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"[/COLOR]


[COLOR=#574123]'FilterUnfilter[/COLOR]
[COLOR=#574123]Rows("6:6").Select[/COLOR]
[COLOR=#574123]Selection.AutoFilter[/COLOR]
[COLOR=#574123]Selection.AutoFilter[/COLOR]



[COLOR=#574123]'START SECOND REPORT[/COLOR]
[COLOR=#574123]'Dynamic range for New PRs added[/COLOR]
[COLOR=#574123]'second filter FINALLY GOT IT[/COLOR]
[COLOR=#574123]Worksheets("Raw Data for PR Dist").Range("A6:GG1048550").AdvancedFilter _[/COLOR]
[COLOR=#574123]Action:=xlFilterInPlace, _[/COLOR]
[COLOR=#574123]CriteriaRange:=Sheets("PR Dist SSD Report").Range("A1", Sheets("PR Dist SSD Report").Range("A" & Rows.Count).End(xlUp)), _[/COLOR]
[COLOR=#574123]Unique:=False[/COLOR]




[COLOR=#574123]Workbooks("PRep.xlsm").Worksheets("PR Dist SSD Report").Activate[/COLOR]


[COLOR=#574123]'Inserting Columns from PR LINE to PR Dist Generated[/COLOR]

[COLOR=#574123]'1 Cancel Date 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Dim n As Integer[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("GL Cancelled Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Cancel Date"[/COLOR]

[COLOR=#574123]'2 Cancel Reason 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Cancel Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Cancel Reason"[/COLOR]


[COLOR=#574123]'3 Closed Date 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Failed Funds Lookup").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Closed Date"[/COLOR]


[COLOR=#574123]'4 Closed Reason 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Closed Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Closed Reason"[/COLOR]

[COLOR=#574123]'5 Contact 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Category").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Contact"[/COLOR]


[COLOR=#574123]'6 Created By 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Distribution").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Created By"[/COLOR]


[COLOR=#574123]'7 Created Date 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Activity Code").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Created Date"[/COLOR]



[COLOR=#574123]'8 Currency 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Amount").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Currency"[/COLOR]

[COLOR=#574123]'9 Currency Amount 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Currency").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Currency Amount"[/COLOR]

[COLOR=#574123]'10 Currency Price 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Currency Amount").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Currency Price"[/COLOR]


[COLOR=#574123]'11 Deliver To 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Document Type").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Deliver To"[/COLOR]


[COLOR=#574123]'12 Destination Organization 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Location").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Destination Organization"[/COLOR]

[COLOR=#574123]'13 Destination Subinventory 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Destination Organization").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Destination Subinventory"[/COLOR]

[COLOR=#574123]'14 Hazard Class 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Destination Subinventory").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Hazard Class"[/COLOR]

[COLOR=#574123]'15 Import Source 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Document Type").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Import Source"[/COLOR]


[COLOR=#574123]'16 Justification 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Buyer").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Justification"[/COLOR]


[COLOR=#574123]'17 Need-By 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Buyer").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Need-By"[/COLOR]


[COLOR=#574123]'18 Note to Buyer 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Buyer").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Note to Buyer"[/COLOR]



[COLOR=#574123]'19 Note to Receiver 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Requester").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Note to Receiver"[/COLOR]

[COLOR=#574123]'20 On RFQ 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Encumbrance Prevented").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "On RFQ"[/COLOR]


[COLOR=#574123]'21 Order Number 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Activity Code").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Order Number"[/COLOR]


[COLOR=#574123]'22 Phone 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Contact").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Phone"[/COLOR]


[COLOR=#574123]'23 Qty Cancelled 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Cancelled").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Qty Cancelled"[/COLOR]



[COLOR=#574123]'24 Qty Delivered 1[/COLOR]

[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Qty Cancelled").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Qty Delivered"[/COLOR]


[COLOR=#574123]'25 Qty Shipped 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Qty Cancelled").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Qty Shipped"[/COLOR]

[COLOR=#574123]'26 Rate 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Variance Account").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Rate"[/COLOR]



[COLOR=#574123]'27 Rate Date 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Rate").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Rate Date"[/COLOR]

[COLOR=#574123]'28 Rate Type 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Rate").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Rate Type"[/COLOR]

[COLOR=#574123]'29 Reference Num 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Location").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Reference Num"[/COLOR]

[COLOR=#574123]'30 RFQ Required1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("On RFQ").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "RFQ Required"[/COLOR]

[COLOR=#574123]'31 Site 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Contact").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Site"[/COLOR]

[COLOR=#574123]'32 Source Type 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Document Type").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Source Type"[/COLOR]



[COLOR=#574123]'33 Supplier 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Contact").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Supplier"[/COLOR]



[COLOR=#574123]'34 Urgent 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Type").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Urgent"[/COLOR]

[COLOR=#574123]'------------------------------------------'----------------'-'-'-'--'-'[/COLOR]

[COLOR=#574123]'35 Cancel Time 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Cancel Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Cancel Time"[/COLOR]


[COLOR=#574123]'36 Created Time 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Created Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Created Time"[/COLOR]



[COLOR=#574123]'37 Closed Time 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Closed Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Closed Time"[/COLOR]



[COLOR=#574123]'38 Need-By Time 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Need-By").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Need-By Time"[/COLOR]



[COLOR=#574123]'39 Rate Time 1[/COLOR]
[COLOR=#574123]'[/COLOR]

[COLOR=#574123]Range("A1").Activate[/COLOR]

[COLOR=#574123]n = ActiveCell.EntireRow.Find("Rate Date").Column[/COLOR]

[COLOR=#574123]Cells(1, n + 1).EntireColumn.Insert[/COLOR]

[COLOR=#574123]' This lets you choose which column cell to place the value after "Kemo" above or below[/COLOR]
[COLOR=#574123]Cells(1, n + 1).Value = "Rate Time"[/COLOR]




[COLOR=#574123]'-------------------------------------------------------------------------[/COLOR]

[COLOR=#574123]'-------------------------------------------------------------------------[/COLOR]


[COLOR=#574123]'First Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("D3:F3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("D2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("D2:F" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]



[COLOR=#574123]'Second Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("J3:L3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("J2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("J2:L" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]



[COLOR=#574123]'Third Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("N3:P3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("N2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("N2:P" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'Foruth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("S3:U3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("S2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("S2:U" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'Fifth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("W3:X3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("W2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("W2:X" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'Sixth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("Z3:AB3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("Z2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("Z2:AB" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'Sivinth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("AF3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("AF2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("AF2:AF" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'eigth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("AN3:AP3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("AN2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("AN2:AP" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]



[COLOR=#574123]'Ninghth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("AR3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("AR2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("AR2:AR" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]



[COLOR=#574123]'Tenth Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("AU3:AX3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("AU2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("AU2:AX" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'eleventh Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("AG3:AJ3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("AG2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("AG2:AJ" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'twelve Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("BL3:BO3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("BL2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("BL2:BO" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]

[COLOR=#574123]'Thirteen Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("BR3:BU3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("BR2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("BR2:BU" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]


[COLOR=#574123]'Fourteen Copy for INDEX Lookup[/COLOR]
[COLOR=#574123]Sheets("DONT TOUCH").Select[/COLOR]
[COLOR=#574123]Range("BW3").Select[/COLOR]
[COLOR=#574123]Selection.Copy[/COLOR]
[COLOR=#574123]Sheets("PR Dist SSD Report").Select[/COLOR]
[COLOR=#574123]Range("BW2").Select[/COLOR]
[COLOR=#574123]ActiveSheet.Paste[/COLOR]
[COLOR=#574123]Application.CutCopyMode = False[/COLOR]
[COLOR=#574123]Selection.AutoFill Destination:=Range("BW2:BW" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#574123]Range(Selection, Selection.End(xlDown)).Select[/COLOR]





[COLOR=#574123]'-------------------------------------------------------------------------[/COLOR]

[COLOR=#574123]'-------------------------------------------------------------------------[/COLOR]




























[COLOR=#574123]'Application.Calculation = xlCalculationAutomatic[/COLOR]



[COLOR=#574123]For Each sht In ThisWorkbook.Worksheets[/COLOR]
[COLOR=#574123]sht.Cells.EntireColumn.AutoFit[/COLOR]
[COLOR=#574123]Next sht[/COLOR]


[COLOR=#574123]ActiveSheet.usedrange[/COLOR]




[COLOR=#574123]'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/COLOR]
[COLOR=#574123]'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/COLOR]

[COLOR=#574123]End Sub[/COLOR]
Kindly highlight any issues or improvement opportunities :)

thanks
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Try to not use Activate or Select
for instance, this

Code: 
Range("C7").Activate
ActiveCell.FormulaR1C1 = "=MID(RC[23],33,4)"

can be

Code: 
Range("C7").FormulaR1C1 = "=MID(RC[23],33,4)"

This

Code: 
Range(Range("D7"), Range("D7").End(xlDown)).Select
Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"

could be

Code: 
Range(Range("D7"), Range("D7").End(xlDown)).NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"

AND

change this

Code: 
Application.Calculation = xlCalculationManual

to

Code: 
with application
.Calculation = xlCalculationManual
.screenupdating = false
end with


and at the end of the code

Code: 
Application.Calculation = xlCalculationautomatic

to

Code: 
with application
.Calculation = xlCalculationautomatic
.screenupdating = true
end with
 
Upvote 0
Michael M said:
Try to not use Activate or Select
for instance, this

Code: 
Range("C7").Activate
ActiveCell.FormulaR1C1 = "=MID(RC[23],33,4)"

can be

Code: 
Range("C7").FormulaR1C1 = "=MID(RC[23],33,4)"

This

Code: 
Range(Range("D7"), Range("D7").End(xlDown)).Select
Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"

could be

Code: 
Range(Range("D7"), Range("D7").End(xlDown)).NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"

AND

change this

Code: 
Application.Calculation = xlCalculationManual

to

Code: 
with application
.Calculation = xlCalculationManual
.screenupdating = false
end with


and at the end of the code

Code: 
Application.Calculation = xlCalculationautomatic

to

Code: 
with application
.Calculation = xlCalculationautomatic
.screenupdating = true
end with
Click to expand...

thanks for your reply.

i must keep the activation as it will activate a formula i have copied otherwise the numbers will not show up thus i wont be able to use advanced filter ( for some reason i have to do this )

for the application.calculation what is the used of it and what is the impact from it on the excel ?

i did not use it but i read its helpful but am afraid it will miss something up !
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
My code is suddenly really slow
Replies
1
Views
375
RobP
R
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
582
Alex Blakenburg
A
J
  • Question Question
Assistance changing VBA code to display as HTML or picture of a selection in Lotus Notes
Replies
2
Views
238
jularobert
J
P
  • Question Question
Help with Vba Code
Replies
3
Views
478
Coyotex3
C
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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