Sub R2R_check_macro_v1()
Dim x As Double
Dim TXT As String
Dim Alert As Worksheet
Dim R2R_Workbook As Workbook
Dim rgFound As Range
Set R2R_Workbook = Workbooks.Open("C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\
R2R_Check_macro - V2.xlsm")
Set DB_Workbook = Workbooks.Open("C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\
DB_Extract_PR.csv")
Set DB_Workbook1 = Workbooks.Open("C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\
DB_Extract_RC.csv")
Set Tkt_Extract = Workbooks.Open("C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\
Ticket_Extract.xls")
R2R_Workbook.Activate
Set Alert = R2R_Workbook.Sheets("Alert")
Set Formula = R2R_Workbook.Sheets("Formula")
Set missing = R2R_Workbook.Sheets("Missing")
Set tkt = Tkt_Extract.Sheets("Page 1")
Set Snow = R2R_Workbook.Sheets("Inc_extract")
Alert.Activate
Alert.Cells.Clear
'MsgBox ("have you saved the alert into txt file")
'**********************************************Opening TXT
file**************************************************************
Open "C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\Extract.txt" For Input As #1
x = 0
Do While Not EOF(1)
Line Input #1, TXT
Alert.Cells(2, 1).Offset(x, 0) = TXT
x = x + 1
Loop
Close #1
'**********************************************Closing TXT
file**************************************************************
'**********************************************Alert
Sheet*******************************************************************
lastrow = Alert.Cells.SpecialCells(xlCellTypeLastCell).Row
' File Format
Alert.Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(21, 1), Array(34, 1),
Array(52, 1), _
Array(63, 1), Array(78, 1)), TrailingMinusNumbers:=True
' Columns("A:A").Select
' Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
' FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(25, 1), Array(35, 1),
Array(53, 1), _
' Array(62, 1), Array(78, 1)), TrailingMinusNumbers:=True
' Title set
Alert.Range("A1").Value = "DC#"
Alert.Range("B1").Value = "Delivery Date"
Alert.Range("C1").Value = "Work Order"
Alert.Range("D1").Value = "Order Number "
Alert.Range("E1").Value = "HDWQty"
Alert.Range("F1").Value = "COMQty"
Alert.Range("G1").Value = "SKU"
Alert.Range("H1").Value = "CONCAT"
Alert.Range("I1").Value = "SKU"
Alert.Range("J1").Value = "SKU & QTY"
Alert.Range("K1").Value = "DD"
'Color Apply
Alert.Range("A1:K1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Alert.Range("A1").Select
Alert.Range(Selection, Selection.End(xlToRight)).Select
Alert.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'Concat apply
Alert.Range("H2").Select
Application.CutCopyMode = False
' ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-5]&RC[-4]"
ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-5]"
Alert.Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lastrow)
Alert.Range("I2").Select
Application.CutCopyMode = False
' ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-5]&RC[-4]"
ActiveCell.FormulaR1C1 = "=RC[-8]&RC[-6]&RC[-2]"
Alert.Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & lastrow)
Alert.Range("J2").Select
Application.CutCopyMode = False
' ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-5]&RC[-4]"
ActiveCell.FormulaR1C1 = "=RC[-9]&RC[-7]&RC[-3]&RC[-4]"
Alert.Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & lastrow)
Alert.Range("K2").Select
Application.CutCopyMode = False
' ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-5]&RC[-4]"
ActiveCell.FormulaR1C1 = "=RC[-10]&RC[-8]&RC[-9]"
Alert.Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & lastrow)
'Remove unwanted lines
'**********************************************************************************
********
Alert.Range("A1:G" & lastrow).AutoFilter Field:=1, Criteria1:="DC#"
Alert.Range("A1:G" & lastrow).Offset(1,
0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Alert.Range("A1:G" & lastrow).AutoFilter Field:=1, Criteria1:="List"
Alert.Range("A1:G" & lastrow).Offset(1,
0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData
Alert.Range("A1:G" & lastrow).AutoFilter Field:=6, Criteria1:="="
Alert.Range("A1:G" & lastrow).Offset(1,
0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData
'**********************************************************************************
********
'**********************************************Alert
Sheet*******************************************************************
'**********************************************Formula
Sheet*****************************************************************
'Get DC List from Txt file for DB result
Formula.Activate
Formula.Cells.Clear
lastrow11 = Alert.Cells(Rows.Count, 1).End(xlUp).Row
Formula.Range("A1:A" & lastrow11).Value = Alert.Range("C1:C" &
lastrow11).Value
Formula.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
lastrow1 = Formula.Cells(Rows.Count, 1).End(xlUp).Row
'Formula creation #1
Formula.Range("B2").Select
ActiveCell.FormulaR1C1 = "=CONCAT(""'"",RC[-1],""',"")"
Selection.AutoFill Destination:=Range("B2:B" & lastrow1)
append = "("
For i = 2 To lastrow1
Value1 = Formula.Range("A" & i).Value
If Value1 = "5830" Then
RC_DC = True
End If
append = append & "'" & Value1 & "',"
Next i
Length = Len(append)
append = Left(append, Length - 1) & ")"
Formula.Range("C2").Select
ActiveCell.FormulaR1C1 = append
'**********************************************Formula Sheet
End*************************************************************
'**********************************************DB
Sheet**********************************************************************
'Run after the DB extract saved
'MsgBox ("Have you saved the Query result into folder")
Set Db = R2R_Workbook.Sheets("DB")
Set Db_Extract = DB_Workbook.Sheets("DB_Extract_PR")
Set Db_Extract1 = DB_Workbook1.Sheets("DB_Extract_RC")
Db.Activate
Db.Cells.Clear
DB_Workbook.Activate
LastRow2 = Db_Extract.Cells.SpecialCells(xlCellTypeLastCell).Row
Db.Range("A1:G" & LastRow2).Value = Db_Extract.Range("A1:G" & LastRow2).Value
Db.Range("H1").Value = "CONCAT"
Db.Range("I1").Value = "SKU"
Db.Range("J1").Value = "SKU & QTY "
Db.Range("K1").Value = "DD"
If RC_DC = True Then
DB_Workbook1.Activate
LastRow3 = Db_Extract1.Cells.SpecialCells(xlCellTypeLastCell).Row
Db_Extract1.Range("A2:G" & LastRow3).Copy Db.Range("A" & LastRow2 + 1)
End If
Db.Activate
Db.Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 9), Array(26, 9)),
TrailingMinusNumbers _
:=True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Db.Range("H2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-4]&RC[-5]"
ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-4]"
Db.Range("H2").Select
LastRow31 = Db.Cells.SpecialCells(xlCellTypeLastCell).Row
Selection.AutoFill Destination:=Db.Range("H2:H" & LastRow31)
'Db.Range("G2:G" & lastrow3 - 1).Select
Db.Range("I2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-4]&RC[-5]"
ActiveCell.FormulaR1C1 = "=RC[-8]&RC[-5]&RC[-3]"
Db.Range("I2").Select
Selection.AutoFill Destination:=Db.Range("I2:I" & LastRow31)
Db.Range("J2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-4]&RC[-5]"
ActiveCell.FormulaR1C1 = "=RC[-9]&RC[-6]&RC[-4]&RC[-3]"
Db.Range("J2").Select
Selection.AutoFill Destination:=Db.Range("J2:J" & LastRow31)
Db.Range("K2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-4]&RC[-5]"
ActiveCell.FormulaR1C1 = "=RC[-10]&RC[-7]&RC[-9]"
Db.Range("K2").Select
Selection.AutoFill Destination:=Db.Range("K2:K" & LastRow31)
'Color Apply
Db.Range("A1:K1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Db.Range("A1:K1").Select
Db.Range(Selection, Selection.End(xlToRight)).Select
Db.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'**********************************************DB Sheet
End******************************************************************
'**********************************************Back Order
Sheet**************************************************************
' Back Order sheet
'MsgBox ("Have you saved the latest Back Order file into Folder")
Set BO = R2R_Workbook.Sheets("Backorder")
BO.Cells.Clear
Set Back_order = Workbooks.Open("C:\Users\a902628\OneDrive - Eviden\Desktop\R2R\
Backorder.xlsx")
Back_order.Activate
GetSheetName = ActiveSheet.Name
Set Back = Back_order.Sheets(GetSheetName)
LastRow21 = Back.Cells.SpecialCells(xlCellTypeLastCell).Row
BO.Activate
BO.Range("J1").Value = "CONCAT"
BO.Range("A1:I" & LastRow21).Value = Back.Range("A1:I" & LastRow21).Value
BO.Range("K1:K" & LastRow21).Value = BO.Range("F1:F" & LastRow21).Value
BO.Range("F1").EntireColumn.Delete
BO.Range("I2").Select
Application.CutCopyMode = False
'ActiveCell.FormulaR1C1 = "=RC[-8]&RC[-6]&RC[-7]"
ActiveCell.FormulaR1C1 = "=RC[-8]&RC[-6]"
BO.Range("I2").Select
Selection.AutoFill Destination:=BO.Range("I2:I" & LastRow21)
BO.Range("A1:J1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
BO.Range("A1").Select
BO.Range(Selection, Selection.End(xlToRight)).Select
BO.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'Db.Range("G2:G" & lastrow3 - 1).Select
'**********************************************Back Order Sheet end
*********************************************************
'**********************************************Missing
Sheet*****************************************************************
missing.Activate
missing.Cells.Clear
missing.Range("A1").Value = "WO Status"
missing.Range("B1").Value = "DC#"
missing.Range("C1").Value = "Delivery Date"
missing.Range("D1").Value = "Work Order"
missing.Range("E1").Value = "Order Number "
missing.Range("F1").Value = "HDWQty"
missing.Range("G1").Value = "COMQty"
missing.Range("H1").Value = "SKU"
missing.Range("I1").Value = "SKU Status"
missing.Range("J1").Value = "SKU QTY status"
missing.Range("K1").Value = "DD Status"
missing.Range("L1").Value = "Final Status"
''Color Apply
missing.Range("A1:L1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
missing.Range("A1").Select
missing.Range(Selection, Selection.End(xlToRight)).Select
missing.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
missing.Range("B2:H" & lastrow11).Value = Alert.Range("A2:G" & lastrow11).Value
missing.Range("A2").Select
'MsgBox ("Finding the Missing/Back order/Available WO ")
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(Alert!RC[7],DB!C[7],1,0)),VLOOKUP(Alert!
RC[7],Backorder!C[8]:C[9],2,0),""Available in HDW""),""Missing in HDW"")"
Selection.AutoFill Destination:=Range("A2:A" & lastrow11)
missing.Range("I2").Select
'MsgBox ("Finding the Missing/Back order/Available WO ")
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(Alert!RC,DB!C,1,0)) = TRUE, ""SKU not found"",VLOOKUP(Alert!
RC,DB!C,1,0))"
Selection.AutoFill Destination:=Range("I2:I" & lastrow11)
missing.Range("J2").Select
'MsgBox ("Finding the Missing/Back order/Available WO ")
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(Alert!RC,DB!C,1,0)) = TRUE, ""SKU- QTY
Mismatch"",VLOOKUP(Alert!RC,DB!C,1,0))"
Selection.AutoFill Destination:=Range("J2:J" & lastrow11)
missing.Range("K2").Select
'MsgBox ("Finding the Missing/Back order/Available WO ")
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(Alert!RC,DB!C,1,0)) = TRUE, ""DD Mismatch"",VLOOKUP(Alert!
RC,DB!C,1,0))"
Selection.AutoFill Destination:=Range("K2:K" & lastrow11)
missing.Range("L2").Select
'MsgBox ("Finding the Missing/Back order/Available WO ")
ActiveCell.FormulaR1C1 = _
"=IF(RC[-11]=""Back Ordered"",""Back Ordered"",IF(RC[-11]=""Missing in
HDW"",""Missing in HDW"",IF(RC[-11]=""Available in HDW"",IF(RC[-3]=""SKU not
found"",""SKU is Missing"",IF(RC[-2]=""SKU- QTY Mismatch"",""SKU QTY is
mismatch"",IF(RC[-1]=""DD Mismatch"",""Delivery Date Mismatch"",""Available in
HDW""))))))"
Selection.AutoFill Destination:=Range("L2:L" & lastrow11)
missing.Range(Selection, Selection.End(xlToRight)).Select
missing.Range(Selection, Selection.End(xlDown)).Select
missing.Columns("A:L").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-27
missing.Range("A1:L1").Select
missing.Range(Selection, Selection.End(xlToRight)).Select
missing.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'**********************************************Missing Sheet
End*************************************************************
' missing.Range("A1:H" & LastRow3).AutoFilter Field:=1, Criteria1:= _
"=Back Ordered", Operator:=xlOr, Criteria2:="=Missing in HDW"
' missing.Range("A1:H" & LastRow3).AutoFilter Criteria1:="=Missing in HDW"
'**********************************************INC
Sheet*********************************************************************
Set INC = R2R_Workbook.Sheets("Inc_Sheet")
INC.Cells.Clear
INC.Activate
INC.Range("A1:D" & lastrow11).Value = missing.Range("B1:E" & lastrow11).Value
INC.Range("E1").Value = "HDW Status"
INC.Range("E1:E" & lastrow11).Value = missing.Range("L1:L" & lastrow11).Value
INC.Range("F1:G" & lastrow11).Value = missing.Range("G1:H" & lastrow11).Value
INC.Range("H1").Value = "Incident Number"
INC.Activate
INC.Range("A1:H" & lastrow11).AutoFilter Field:=5, Criteria1:= _
Array("Available in HDW", "="), Operator:=xlFilterValues
INC.Range("A2").Select
INC.Range(Selection, Selection.End(xlToRight)).Select
INC.Range(Selection, Selection.End(xlDown)).Select
INC.Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
'* ActiveSheet.ShowAllData
INC.Range("A1:H1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
INC.Range("A1").Select
INC.Range(Selection, Selection.End(xlToRight)).Select
INC.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'INC.Columns("A:F").Select
' ActiveSheet.Range("A1:F" & lastrow11).RemoveDuplicates Columns:=Array(1, 2, 3,
4, 5, 6) _
' , Header:=xlYes
INC.Activate
'Fin = INC.Cells.SpecialCells(xlCellTypeLastCell).Row
Fin = Application.CountA(INC.Range("A:A"))
'**********************************************Ticket
Extract**************************************************************
Snow.Cells.Clear
tkt.Activate
tktrow = tkt.Cells.SpecialCells(xlCellTypeLastCell).Row
Snow.Activate
Snow.Range("A1:A" & tktrow).Value = tkt.Range("A1:A" & tktrow).Value
Snow.Range("B1:B" & tktrow).Value = tkt.Range("J1:J" & tktrow).Value
Snow.Range("C1:C" & tktrow).Value = tkt.Range("C1:C" & tktrow).Value
Snow.Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"
Snow.Cells.Select
Snow.Cells.EntireColumn.AutoFit
Selection.ColumnWidth = 104.55
ActiveWindow.SmallScroll Down:=1
Snow.Cells.EntireRow.AutoFit
Snow.Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-1
Snow.Range("A1:C1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Snow.Range("A1").Select
Snow.Range(Selection, Selection.End(xlToRight)).Select
Snow.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
If Fin > 1 Then
For i = 2 To Fin
For j = 2 To tktrow
If InStr(1, Snow.Range("B" & j), INC.Range("C" & i)) <> 0 Then
INC.Range("H" & i).Value = Snow.Range("A" & j)
Exit For
Else
End If
Next j
Next i
End If
DB_Workbook1.Close
Tkt_Extract.Close
Back_order.Close
DB_Workbook.Close
INC.ShowAllData
MsgBox (" Macro completed , Please proceed to check and create ticket")
End Sub
