Hi i am wondering if someone could help me. I currently have a sheet called UPDATE_SHEET containing my data. When a Macro is run called UPDATE() what happens is, data is brought in from other worksheets (sched.xls). The data replaces current data from the range A:P. This data is dynamic and will change over time. What I want is column Q (COMMENTS heading) to have a summary for the row of information. This will be typed in. However when the Macro updates and new data is brought in I want to make sure the COMMENTS will be on the same row originally and will not be altered.
I have a primary key that is unique every time in column L called BARCODE #. The data in sched.xls will change from time to time and when this is copied over to UPDATE_SHEET, i want to make sure that data in COMMENTS column is referring to the same BARCODE # before and after the update.
Would anyone have any idea?
Please view code below
[VBA]
Sub UPDATE()
Application.DisplayAlerts = False
Sheets("UPDATE_SHEET").Select
Cells.Select
Selection.ClearContents
ChDir "T:\FinanceDept\WEEKLYFILES"
Sheets("UPDATE_SHEET").Select
Workbooks.Open Filename:= _
"T:\FinanceDept\WEEKLYFILES\sched.xls"
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("CURRENT.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("sched.xls").Activate
ActiveWorkbook.Close
Sheets("UPDATE_SHEET").Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
ActiveCell.FormulaR1C1 = "NAME"
Range("B1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Range("C1").Select
ActiveCell.FormulaR1C1 = "ORDER #"
Range("D1").Select
ActiveCell.FormulaR1C1 = "ORDER RELEASE DATE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ORDER DUE DATE"
Range("F1").Select
ActiveCell.FormulaR1C1 = "ORDER START DATE"
Range("G1").Select
ActiveCell.FormulaR1C1 = "ORDER FINISHED DATE"
Range("H1").Select
ActiveCell.FormulaR1C1 = "OPERATION #"
Range("I1").Select
ActiveCell.FormulaR1C1 = "OPERATION DESCRIPTION"
Range("J1").Select
ActiveCell.FormulaR1C1 = "OPERATION RELEASE DATE"
Range("K1").Select
ActiveCell.FormulaR1C1 = "OPERATION DUE DATE"
Range("L1").Select
ActiveCell.FormulaR1C1 = "BARCODE #"
Range("M1").Select
ActiveCell.FormulaR1C1 = "BARCODE STATUS"
Range("N1").Select
ActiveCell.FormulaR1C1 = "BARCODE AVAILABLE"
Range("O1").Select
ActiveCell.FormulaR1C1 = "BARCODE ON-HAND"
Range("P1").Select
ActiveCell.FormulaR1C1 = "BARCODE NOTE #"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "TEXT SUMMARY"
Range("A1:Q1").Select
Range("Q1").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.Bold = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Columns("A:Q").Select
Range("Q1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.DisplayZeros = False
Cells.Select
Cells.EntireColumn.AutoFit
Sheets("UPDATE_SHEET").Select
Columns("A:B").Select
Selection.Interior.ColorIndex = 35
Columns("C:J").Select
Columns("C:K").Select
Selection.Interior.ColorIndex = 6
Columns("L:Q").Select
Selection.Interior.ColorIndex = 40
Range("Q9").Select
Range("B1:Q1").Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Selection.Interior.ColorIndex = xlNone
Range("B10").Select
Application.CommandBars("Stop Recording").Visible = False
Application.DisplayAlerts = False
End sub
[/VBA]