Help In Identifying "Object Not Defined" Error

[evxret]

Hello all,
I am currently attempting to write a macro that filters out New orders, (Anything with N/A in column 11, as shown as first part of code), Copies any visible (filtered) cells, and pastes them onto the first empty line on Column B on another sheet.

Here is what I have written here.

'   ``Filter New Orders
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("$A$1:$K$1000").AutoFilter Field:=11, Criteria1:="#N/A"
    
    ''Set Variables
    Dim LR As Integer
     Set LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row
    

    
    ''Copy Customer Cells After Filter has been applied
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("C2:C").SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
   

    ''Pastes Customer Info on First Open Cell on Column D (First Open Cell Variable pulled from Column C)  
    Sheets("ALL Orders Database").Activate
    Worksheets("ALL Orders Database").Range("D2:D" & LR + 1).PasteSpecial Paste:=xlPasteValues

When I Run the code, it highlights my "Set LR" line and gives me "Object not defined" Error, I've tried to Dim LR as an integer, long, I've tried adjusting the code defining my row count.

 

[lrobbo314]

Try changing

Set LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row

to

LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row

 

[evxret]

Try changing

Set LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row

to

LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row

Thank you! This helped. I am now though, getting a 1004 runtime error, I had to fix the code in a few other spots so here is the new example.

'   ``Filter New Orders
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("$A$1:$K$1000").AutoFilter Field:=11, Criteria1:="#N/A"
    
    ''Set Variables
    Dim LR As Long
    Dim DBLR
    LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row
    DBLR = Sheets("Test Ship Report").Range("B2").End(xlDown).Row

    
    ''Copy Customer
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("C2:C" & DBLR + 1).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("ALL Orders Database").Activate
    Worksheets("ALL Orders Database").Range("D2:D" & LR + 1).PasteSpecial Paste:=xlPasteValues

It is highlighting the very last line in the code as the error. Can you spot anything wrong just by looking at it? I appreciate your help so much.

 

[evxret]

Thank you! This helped. I am now though, getting a 1004 runtime error, I had to fix the code in a few other spots so here is the new example.

'   ``Filter New Orders
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("$A$1:$K$1000").AutoFilter Field:=11, Criteria1:="#N/A"
   
    ''Set Variables
    Dim LR As Long
    Dim DBLR
    LR = Sheets("ALL Orders Database").Range("C2").End(xlDown).Row
    DBLR = Sheets("Test Ship Report").Range("B2").End(xlDown).Row

   
    ''Copy Customer
    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("C2:C" & DBLR + 1).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("ALL Orders Database").Activate
    Worksheets("ALL Orders Database").Range("D2:D" & LR + 1).PasteSpecial Paste:=xlPasteValues

It is highlighting the very last line in the code as the error. Can you spot anything wrong just by looking at it? I appreciate your help so much.

Just noticed my 2nd variable was missing a declaration, Fixed it and same issue.

 

[Fluff]

Are you trying to paste the data into D2?

 

[evxret]

Are you trying to paste the data into D2?

I am trying to paste the data into column D, based on the first open cell in Column C. Information is completely filled in Column C which is why it will be the identifier for where to paste data. (Column D has blanks so If I try to set a variable to find the last row it will not be able to find it, because technically its sometimes blank.) I hope this makes sense.

 

[Fluff]

Ok, how about

    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("C2:C" & DBLR + 1).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Worksheets("ALL Orders Database").Range("D" & LR).PasteSpecial Paste:=xlPasteValues

 

[evxret]

Ok, how about

    Sheets("Test Ship Report").Activate
    Sheets("Test Ship Report").Range("C2:C" & DBLR + 1).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Worksheets("ALL Orders Database").Range("D" & LR).PasteSpecial Paste:=xlPasteValues

This worked, Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.