Prashant1211
New Member
- Joined
- Jun 9, 2020
- Messages
- 33
- Office Version
- 2016
- Platform
- Windows
Dear all, I am trying to create a OTD list for projects dispatched. I have a excel application which is used to dispatch the project. So what i am trying is the OTD list (another workbook) should be updated with project name and dispatch date once the project is dispatched from the application. below is my code.
Problem i am facing is if the same project is dispatched again then code create one more entry in OTD list in next row rather then searching if the project already exist. and if the project already exist than it should add the next date in-front of the project in next available column (This is required because project is dispatched in n number of lots). and if not then should work as per below code.
Can someone please help me in this. Thanks for your support.
Sub OTD()
Dim x As Workbook
Dim LR As Integer
Dim answer As Integer
answer = MsgBox("Update OTD List?", vbQuestion + vbYesNo)
If answer = vbYes Then
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Main Sheet").Range("B5").Copy 'project no. from main application copied
Set x = Workbooks.Open("File location OTD list") ' otd list open
LR = x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Row
x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Select
Selection.Value = Date
Selection.NumberFormat = "dd/mm/yyyy"
'Close x:
x.Save
x.Close
Else
End If
End Sub
Problem i am facing is if the same project is dispatched again then code create one more entry in OTD list in next row rather then searching if the project already exist. and if the project already exist than it should add the next date in-front of the project in next available column (This is required because project is dispatched in n number of lots). and if not then should work as per below code.
Can someone please help me in this. Thanks for your support.
Sub OTD()
Dim x As Workbook
Dim LR As Integer
Dim answer As Integer
answer = MsgBox("Update OTD List?", vbQuestion + vbYesNo)
If answer = vbYes Then
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Main Sheet").Range("B5").Copy 'project no. from main application copied
Set x = Workbooks.Open("File location OTD list") ' otd list open
LR = x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Row
x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
x.Sheets("OTD").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Select
Selection.Value = Date
Selection.NumberFormat = "dd/mm/yyyy"
'Close x:
x.Save
x.Close
Else
End If
End Sub