Copy specific excel data to another excel workbook with condition.

[Xlacs]

Hi,

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.

Please.. Someone help me on this. =(

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

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Submit Excel Data to Another Workbook withj Condition - OzGrid Free Excel/VBA Help Forum

Hello Everyone,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…

forum.ozgrid.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Also posted here Help: Copy specific excel data to another workbook if condition met

If you have posted the question at more places, please provide links to those as well.