VBA Copy and Filter Issue(s) 'Range' of Object Error and copying the wrong line

[amimeoru]

Hi There,
I am new to forum but have spent a while building a automated purchase order and more system following the instructions. The purchase and sales orders work great but I have tried to modify a few more to make and assembly builder and Quote uploader. I have two issues with both these now, if anyone has an idea that would be greatly appreciated as I am currently now trying to work with all of these...

1) When I try to upload a previous sheet I get issues with uploading the date correctly. I get a Method 'Range' of object _Worksheet' failed issue. Below is the program I am using for the load. The issues comes up on the highlighted part.

Sub Assembly_Load()
Dim AssemblyRow As Long, AssemblyItemRow As Long, LastAssemblyItemRow As Long
Dim ResultRow As Long, LastItemResultRow As Long
With Sheet31
If .Range("B3").Value = Empty Then
MsgBox "Please Select a Current Assembly #"
Exit Sub
End If
AssemblyRow = .Range("B3").Value 'Assembly Row
.Range("D8:H40,J8:J40").ClearContents 'Clear Assembly Fields
.Range("B5").Value = True 'Set Assembly Load to True
.Range("K4").Value = Sheet32.Range("C" & AssemblyRow).Value 'Assembly Date
.Range("K5").Value = Sheet32.Range("E" & AssemblyRow).Value 'Assembly Type
.Range("K3").Value = Sheet32.Range("D" & AssemblyRow).Value 'Assembly Factor
.Range("H6").Value = Sheet32.Range("B" & AssemblyRow).Value 'Assembly Name
.Range("I42").Value = Sheet32.Range("G" & AssemblyRow).Value 'Mat. Adj %
.Range("K42").Value = Sheet32.Range("J" & AssemblyRow).Value 'Labour Rate

'Load in Assembly Items
LastAssemblyItemRow = Sheet33.Range("A99999").End(xlUp).Row 'Last Item Row
If LastAssemblyItemRow < 3 Then GoTo NoItems
'Criteria Advanced Filter
Sheet33.Range("A2:I" & LastAssemblyItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:= _
Sheet33.Range("K2:K3"), CopyToRange:=Sheet33.Range("M2:U2"), Unique:=False
LastItemResultRow = Sheet33.Range("M99999").End(xlUp).Row 'Last Results Row
If LastItemResultRow < 3 Then GoTo NoItems
For ResultRow = 3 To LastItemResultRow
AssemblyItemRow = Sheet33.Range("T" & ResultRow).Value 'Assembly Item Row
.Range("D" & AssemblyItemRow).Value = Sheet33.Range("N" & ResultRow).Value 'Qty
.Range("E" & AssemblyItemRow).Value = Sheet33.Range("O" & ResultRow).Value 'Desc
.Range("F" & AssemblyItemRow).Value = Sheet33.Range("P" & ResultRow).Value 'Part#
.Range("G" & AssemblyItemRow).Value = Sheet33.Range("Q" & ResultRow).Value 'UOM
.Range("H" & AssemblyItemRow).Value = Sheet33.Range("R" & ResultRow).Value 'Price
.Range("L" & AssemblyItemRow).Value = Sheet33.Range("U" & ResultRow).Value 'Item Database Row
.Range("J" & AssemblyItemRow).Value = Sheet33.Range("S" & ResultRow).Value 'Labour
Next ResultRow

NoItems:
.Range("B5").Value = False 'Set Assembly Load To False
.Range("B4").Value = False 'Existing Assembly
.Shapes("CancelNewBtn").Visible = msoFalse
.Shapes("AddNewBtn").Visible = msoCTrue
End With
End Sub

2) This one I am searching a column to find the last row with data to copy the information. Since multiple rows between data are blank I have tried to skip those rows. It copies and identifies the row / database row properly however it seems to copy the header from the row above instead of the row I wish to copy.

'Add/Update Quote Item
lastItemRow = .Range("D58").End(xlUp).Row 'Last Item Row
If lastItemRow < 11 Then GoTo NoItems
For ItemRow = 11 To lastItemRow
If .Range("K" & ItemRow).Value <> Empty Then 'Existing Row
QuoteItemRow = .Range("K" & ItemRow).Value 'Existing
ElseIf .Range("D" & ItemRow).Value > 0 Then 'New Row
QuoteItemRow = Sheet12.Range("A999999").End(xlUp).Row + 1 'First Avail Item Row
Sheet12.Range("A" & QuoteItemRow).Value = Sheet12.Range("L3").Value 'Quote Row Number
Sheet12.Range("B" & QuoteItemRow).Value = .Range("J2").Value 'Quote Number
Sheet12.Range("H" & QuoteItemRow).Value = ItemRow 'Row Number
Sheet12.Range("I" & QuoteItemRow).Value = "=Row()" 'Database Row
.Range("K" & ItemRow).Value = QuoteItemRow
End If
Sheet12.Range("C" & QuoteItemRow & ":F" & QuoteItemRow).Value = Range("D" & ItemRow & ":G" & ItemRow).Value 'Qty, Desc, Factor & Price
Sheet12.Range("J" & QuoteItemRow).Value = .Range("J3").Value 'Job Number
Sheet12.Range("G" & QuoteItemRow).Value = .Range("I" & ItemRow).Value 'Labour
Next ItemRow

NoItems:
.Range("B4").Value = False 'Existing Quote
.Shapes("CancelNewBtn").Visible = msoFalse
.Shapes("AddNewBtn").Visible = msoCTrue
End With
End Sub

Thank you for taking the time to help. I am guessing I am not using the correct method of code now that I have altered the original styles and may need to utilize a different type of code. Hope this makes sense and someone is able to help.

 

[bebo021999]

With this line upper:

AssemblyItemRow = Sheet33.Range("T" & ResultRow).Value 'Assembly Item Row

Make sure that column T value is number:
Sheet33.Range("T" & ResultRow).Value

 

[amimeoru]

No luck set the column to number and same issue. When I reset the VBA it does load all the correct info in however it sets the Load to True which does not allow me to make changes. Thank you for the quick response!

 

[bebo021999]

With this line upper:

AssemblyItemRow = Sheet33.Range("T" & ResultRow).Value 'Assembly Item Row

Make sure that column T value is number:
Sheet33.Range("T" & ResultRow).Value

What I actual mean is:

AssemblyItemRow must be row index, like, 1,2,3,4,... (value)

then, check to see:
what is the value in column T sheet 33? Are they values? If not, it is the issue.

 

[amimeoru]

Right on! Okay so the end of my table rows were empty. I dragged the table up to the last item and it has fixed the issue. Thank you so much. It’s funny it was that easy. Appreciate it! I think the 2nd issue shouldn’t be too bad that one was just getting me.