MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
I am kinda new to this VBA programming so I am wondering if someone could help me with this simple problem.
So I have a time and motion study tracker with start, end and submit button button. I managed to worked on start and end.
But I am struggling to submit an specific data to my masterfile. In my workbook. I have a dropdown selection of status.. If the value of the dropdown is Pending, then those cases should be submitted in my Archive workbook when I hit the Submit button and the status in my last column will be updated to Submitted. So it will exclude those cases on my next submission and should not overlap the data in master file.
Below is my screenshot of source and masterfile and also my codes.
Thank you in Advance!
Sub Submit() Dim STunsent As String Dim OA As Object Dim msg As Object Dim sh As Worksheet Dim Outapp As Object, Logfile As String Dim r As Long, lr As Long, Archive As Workbook sh = ThisWorkbook.Sheets("Prod") Dim x As Integer Dim lastrow As Integer lastrow = Application.WorksheetFunction.CountBlank(sh.Range("D:D")) For x = 2 To lastrow If sh.Range("O" & i).Value <> "Submitted" And sh.Range("J" & i).Value = "Pending" Then Set Archive = Workbooks.Open("C:\Users\ChrisLacs\Desktop\Test\Archive.xlsm") r = 2 lr = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(r, 1), Cells(r, 3)).Copy Active.Paste Destination:=Archive.Worksheets("Master").Rows(erow) sh.Range("O" & i).Value = "Submitted" End If r = r + 1 Archive.Close Next End Sub