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.
Kindly highlight any issues or improvement opportunities
thanks
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]
thanks