Extract/Move row based condition

K

Keysha_bee

New Member
Joined
Feb 26, 2016
Messages
19
Hello Everyone,

I have searched this forum and others for several days but can't find what I needed so I took a stab and trying to code myself and it was an epic fail....

I have two workbooks wb1 (has master data in it) and wb2 (storage data). Here's what I'm trying to do...

-If column AA in wb1,ws1 "Jan - Aug" = "Validation Complete", cut the entire row
- Open wb2,ws2 "Sheet1", find and paste on the last row
-Loop through wb1,ws1

Here's the code I'm trying to use...
Code: 
Sub Status()
Dim i As Long
Dim j As Long
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim Status As String
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set wb2 = Workbooks.Open("C:\Users\lakeit\Desktop\Lubes Validated Data.xlsm")
Set ws2 = Sheets("Sheet1")
Set wb1 = Workbooks("All Lubes Test - Master Data.xlsm")
Set ws1 = ThisWorkbook.Sheets("Jan - Aug")
 
'Let’s start at row 2. Row 1 has headers
For i = 2 To lastrow1
For j = 2 To lastrow2
'Start the loop
'Do While Cells(i, 1).Value <> ""
'Look for data with ‘Validation’
    If wb1.Sheet("Jan - Aug").Cells(i, "AA").Value = "Validation Complete" Then
'copy the row if it contains ‘Car’
     wb1.ws1.Sheets("Jan - Aug").Range(Cells(i, "A"), Cells(i, "AI")).Cut
'Go to sheet2. Activate it. We want the data here
    wb2.ws2.Sheets("Sheet1").Activate
    
    wb2.ws2.Sheets("Sheet2").Range(Cells(j, "A"), Cells(j, "AI")).Select
'Find the first empty row in sheet2
    lastrow2 = wb2.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Paste the data here
    ActiveSheet.Paste Destination:=wb2.Sheets("Sheet1").Rows(lastrow2)
   End If
   'go to sheet1 again and actvate it
wb1.ws1.Sheets("Jan - Aug").Activate
'Loop through the other rows with data
    
    Next j
    Application.CutCopyMode = False
    Next I

(I am placing a small data set here but the real version I of the master data has several worksheet with data and formulas)[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 4389"]
<tbody>[TR]
[TD]Invoice Number
[/TD]
[TD]Invoice Number2
[/TD]
[TD]Sold-to party
[/TD]
[TD]Shipping Date
[/TD]
[TD]Material
[/TD]
[TD]Mode of transport
[/TD]
[TD]Ship-to party
[/TD]
[TD]Dest. Country
[/TD]
[TD]Co. code
[/TD]
[TD]Billing doc. date
[/TD]
[TD]Route
[/TD]
[TD]Customs Qty 1 (Gal)
[/TD]
[TD]Sum of Invoice
[/TD]
[TD]Agent's Name
[/TD]
[TD]Concat
[/TD]
[TD]Filed w/I Time Period
[/TD]
[TD]Days
[/TD]
[TD]Night
[/TD]
[TD]Evening
[/TD]
[TD]Hazardous
[/TD]
[TD]Port
[/TD]
[TD]Destination Country
[/TD]
[TD]Type
[/TD]
[TD]Volume
[/TD]
[TD]Value
[/TD]
[TD]Freight
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1058899
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]2420
[/TD]
[TD]16020.4
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1057265
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]550
[/TD]
[TD]3300
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1042786
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]262.629
[/TD]
[TD]2489.2
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193040
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1058870
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]140
[/TD]
[TD]1118.6
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1055933
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]1320
[/TD]
[TD]9385.2
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056544
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]9.358
[/TD]
[TD]212.45
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193750
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056664
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]288
[/TD]
[TD]3736.8
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193750
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559839
[/TD]
[TD]967035861
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056667
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]233.333
[/TD]
[TD]3272.5
[/TD]
[TD]Off
[/TD]
[TD]246559839-2710193750
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1057236
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]36
[/TD]
[TD]391.68
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1058912
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]40
[/TD]
[TD]339.6
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1057265
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]2915
[/TD]
[TD]17490
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1058840
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]220
[/TD]
[TD]1870
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1062630
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]33
[/TD]
[TD]368.94
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1052775
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]72
[/TD]
[TD]586.08
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1045735
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]140
[/TD]
[TD]1358
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1055934
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]280
[/TD]
[TD]2088.8
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1055907
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]440
[/TD]
[TD]3418.8
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1057408
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]220.727
[/TD]
[TD]2058.2
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1055911
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]220
[/TD]
[TD]1709.4
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056801
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]70
[/TD]
[TD]561.4
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1055927
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]220
[/TD]
[TD]1564.2
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1045856
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]39
[/TD]
[TD]500.37
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193080
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Value Incorrect
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056544
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]18.717
[/TD]
[TD]424.9
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193750
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246559840
[/TD]
[TD]967040172
[/TD]
[TD]10004828
[/TD]
[TD]1/7/2016
[/TD]
[TD]1056664
[/TD]
[TD]Truck
[/TD]
[TD]20029110
[/TD]
[TD]CR
[/TD]
[TD]LL
[/TD]
[TD]1/11/2016
[/TD]
[TD]May LOGISTICS INC
[/TD]
[TD]288
[/TD]
[TD]3736.8
[/TD]
[TD]Off
[/TD]
[TD]246559840-2710193750
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]Validation Incomplete
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1058900
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]420
[/TD]
[TD]2520
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1057228
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]108
[/TD]
[TD]1150.2
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1071515
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]220
[/TD]
[TD]1210
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1061893
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]600
[/TD]
[TD]4362
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1057266
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]210
[/TD]
[TD]1526.7
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1071516
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]210
[/TD]
[TD]1239
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193020
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]246588684
[/TD]
[TD]967053236
[/TD]
[TD]10083367
[/TD]
[TD]1/11/2016
[/TD]
[TD]1048943
[/TD]
[TD]Truck
[/TD]
[TD]20069222
[/TD]
[TD]AU
[/TD]
[TD]LL
[/TD]
[TD]1/12/2016
[/TD]
[TD]CH
[/TD]
[TD]19.697
[/TD]
[TD]1617
[/TD]
[TD]Off
[/TD]
[TD]246588684-2710193040
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Wrong Port
[/TD]
[TD]OK
[/TD]
[TD]OK
[/TD]
[TD]Volume Incorrect
[/TD]
[TD]OK
[/TD]
[TD]TRUE
[/TD]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Give this a try on a copy of the workbooks

Code: 
Sub Status()
    Dim i As Long
    Dim j As Long
    Dim lastrow1 As Long
    Dim lastrow2 As Long
    Dim Status As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Application.ScreenUpdating = False
    
    Set wb1 = ThisWorkbook
    Set ws1 = ThisWorkbook.Sheets("Jan - Aug")
    Set wb2 = Workbooks.Open("C:\Users\lakeit\Desktop\Lubes Validated Data.xlsm")
    Set ws2 = wb2.Sheets("Sheet1")
     
     lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
     
    'Let’s start at row 2. Row 1 has headers
    For i = lastrow1 To 2 Step -1
        If ws1.Cells(i, "AA").Value = "Validation Complete" Then
            'copy the row if it contains ‘Car’
            ws1.Range(ws1.Cells(i, "A"), ws1.Cells(i, "AI")).Cut
            'Go to sheet2. Activate it. We want the data here
            wb2.Activate
            ws2.Activate
                
            'Find the first empty row in sheet2
            lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
            'Paste the data here
            ActiveSheet.Paste Destination:=ws2.Cells(lastrow2, 1)
            ws1.Rows(i).Delete shift:=xlUp
        End If
        
        'go to sheet1 again and actvate it
     
        'Loop through the other rows with data
    Next i
    Application.CutCopyMode = False

End Sub
 
Upvote 0
Momentman said:
Give this a try on a copy of the workbooks

Rich (BB code): 
Sub Status()
    Dim i As Long
    Dim j As Long
    Dim lastrow1 As Long
    Dim lastrow2 As Long
    Dim Status As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Application.ScreenUpdating = False
    
    Set wb1 = ThisWorkbook
    Set ws1 = ThisWorkbook.Sheets("Jan - Aug")
    Set wb2 = Workbooks.Open("C:\Users\lakeit\Desktop\Lubes Validated Data.xlsm")
    Set ws2 = wb2.Sheets("Sheet1")
     
     lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
     
    'Let’s start at row 2. Row 1 has headers
    For i = lastrow1 To 2 Step -1
        If ws1.Cells(i, "AA").Value = "Validation Complete" Then
            'copy the row if it contains ‘Car’
            ws1.Range(ws1.Cells(i, "A"), ws1.Cells(i, "AI")).Cut
            'Go to sheet2. Activate it. We want the data here
            wb2.Activate
            ws2.Activate
                
            'Find the first empty row in sheet2
            lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
            'Paste the data here
            ActiveSheet.Paste Destination:=ws2.Cells(lastrow2, 1)
            ws1.Rows(i).Delete shift:=xlUp
        End If
        
        'go to sheet1 again and actvate it
     
        'Loop through the other rows with data
    Next i
    Application.CutCopyMode = False

End Sub
Click to expand...

I am getting a Type Mismatch at the highlighted text
 
Upvote 0
I am thinking its 'cos of those #N/A errors that you have

Lets modify it this way
Code: 
    Sub Status()
        Dim i As Long
        Dim j As Long
        Dim lastrow1 As Long
        Dim lastrow2 As Long
        Dim Status As String
        Dim wb1 As Workbook, wb2 As Workbook
        Dim ws1 As Worksheet, ws2 As Worksheet
        Application.ScreenUpdating = False
        
        Set wb1 = ThisWorkbook
        Set ws1 = ThisWorkbook.Sheets("Jan - Aug")
        Set wb2 = Workbooks.Open("C:\Users\lakeit\Desktop\Lubes Validated Data.xlsm")
        Set ws2 = wb2.Sheets("Sheet1")
         
         lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
         
        'Let’s start at row 2. Row 1 has headers
        
        For i = lastrow1 To 2 Step -1
            On Error Resume Next
            If ws1.Cells(i, "AA").Value = "Validation Complete" Then
                'copy the row if it contains ‘Car’
                ws1.Range(ws1.Cells(i, "A"), ws1.Cells(i, "AI")).Cut
                'Go to sheet2. Activate it. We want the data here
                wb2.Activate
                ws2.Activate
                    
                'Find the first empty row in sheet2
                lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
                'Paste the data here
                ActiveSheet.Paste Destination:=ws2.Cells(lastrow2, 1)
                ws1.Rows(i).Delete shift:=xlUp
            End If
            
            'go to sheet1 again and actvate it
         
            'Loop through the other rows with data
        Next i
        Application.CutCopyMode = False

    End Sub
 
Upvote 0
Thanks Momentman for your assistance it is greatly appreciated...

I am moving the code into the actual SS and set the permanent location for wb2 but I am getting Error 91: Object variable...not set message. The permanent location for wb2 is on our network in a shared folder. Do you think this has anything to do with it?

The macro stops at:
lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
 
Last edited:
Upvote 0
Keysha_bee said:
Thanks Momentman for your assistance it is greatly appreciated...

I am moving the code into the actual SS and set the permanent location for wb2 but I am getting Error 91: Object variable...not set message. The permanent location for wb2 is on our network in a shared folder. Do you think this has anything to do with it?

The macro stops at:
lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
Click to expand...

Can you confirm the name of that sheet, i.e the "Jan - Aug", is the name in the code exactly same as the name of the sheet
 
Upvote 0
It is the same name.

"Jan - Aug"

But I did create an active control on a summary sheet and called it from the module. Do you think that could have affected something?
 
Upvote 0
Yesterday, you were the getting error on this line
Rich (BB code): 
If ws1.Cells(i, "AA").Value = "Validation Complete" Then


Now it seems not to be getting to that line of code. Can i see the code as you have it and have you tried just running the code normally from the VBE(Visual Basic Editor)
 
Upvote 0
Momentman said:
Yesterday, you were the getting error on this line
Rich (BB code): 
If ws1.Cells(i, "AA").Value = "Validation Complete" Then


Now it seems not to be getting to that line of code. Can i see the code as you have it and have you tried just running the code normally from the VBE(Visual Basic Editor)
Click to expand...


Code: 
Sub Status()    
        Dim i As Long
        Dim j As Long
        Dim lastrow1 As Long
        Dim lastrow2 As Long
        Dim Status As String
        Dim wb1 As Workbook, wb2 As Workbook
        Dim ws1 As Worksheet, ws2 As Worksheet
        Application.ScreenUpdating = False

       Set wb1 = ThisWorkbook
        Set ws1 = ThisWorkbook.Sheets("Jan - Aug")
        Set wb2 = Workbooks.Open("\\D6.net\HST_SHARED\P\Lubes.xlsx")
        Set ws2 = wb2.Sheets("Sheet1")
         
         lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row


         For i = lastrow1 To 2 Step -1

         On Error Resume Next
            If ws1.Cells(i, "AA").Value = "Validation Complete" Then
                'copy the row if it contains ‘Status’
                ws1.Range(ws1.Cells(i, "A"), ws1.Cells(i, "AI")).Cut
                'Go to sheet2. Activate it. We want the data here
                wb2.Activate
                ws2.Activate
                    
                'Find the first empty row in sheet2
                lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
                'Paste the data here
                ActiveSheet.Paste Destination:=ws2.Cells(lastrow2, 1)
                ws1.Rows(i).Delete shift:=xlUp
            End If

              'go to sheet1 again and actvate it
         
            'Loop through the other rows with data
        'Next i
        Application.CutCopyMode = False

  End Sub
[COLOR=#333333] [/COLOR]
 
Upvote 0
Have you tried it with a file not on the network drive?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
String running first time but when looping back it isnt placing correctly.
Replies
1
Views
502
DanSMT
D
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
E
  • Question Question
Extracting part of a string in a column of cells (Site IDs) for each cell in a row and then moving down
Replies
0
Views
553
Excel_Ninja
E
M
  • Question Question
VBA code to Copy & Paste Values in Last Row of Table
Replies
0
Views
2K
mikepz888
M
B
  • Question Question
3 three CASCADING DATA VALIDATION (CATEGORY, DESCRIPTION and MODEL) PLEASE HELP ! ! ! ! ! ! Thank you and GOD BLESS....
2
Replies
14
Views
7K
Brixton11
B

Forum statistics

Threads
1,223,445
Messages
6,172,181
Members
452,447
Latest member
willsing5130

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top