Error in last row

[Haree]

Hello all, I have the following code. I have certain errors in last row calculation. Should I redefine last row before I use it every time or does it recalculate every time I call lastrow on its own. Any help will be greatly appreciated. Thanks
Attaching the full code and the particular lines I have trouble in as well.

Sub transfer()
'Transfer modified and aligned data to Daily Sheet
Application.ScreenUpdating = False
Dim ws, ws2 As Worksheet
Dim Range1, Range2, Rangecopy As Range
Dim lr As Long
Dim RngF, RngG, RngH, RngI, RngJ, RngK As Range

Set ws = ThisWorkbook.Worksheets("Daily Extract")
Set ws2 = ThisWorkbook.Worksheets("Data Sheet")
lr = Range("A" & Rows.Count).End(xlUp).Row
Set Range1 = ws.Range("A1:K" & lr)
Set Range2 = ws2.Range("A" & lr + 1)

Set RngF = ws.Range("F2:F" & lr)
Set RngG = ws.Range("G2:G" & lr)
Set RngH = ws.Range("H2:H" & lr)
Set RngI = ws.Range("I2:I" & lr)
Set RngJ = ws.Range("J2:J" & lr)
Set RngK = ws.Range("K2:K" & lr)

RngF.Formula = "=VLOOKUP(C2,'Master Sheet'!$A$2:$E$25,2,FALSE)"
RngG.Formula = "=A2"
RngH.Formula = "Sales"
RngI.Formula = "=D2"
RngJ.Formula = "=E2"
RngK.Formula = "=VLOOKUP(C2,'Master Sheet'!$A$2:$E$25,5,FALSE)"

    With ws
    
        With Range1
                .AutoFilter 3, "="
                    lr = Range("A" & Rows.Count).End(xlUp).Row
                    If lr > 1 Then
                    Range("A2:A" & lr).Delete Shift:=xlUp
                    End If
                .AutoFilter
        End With
        
        With Range1
                .AutoFilter 11, "Weight"
                    lr = Range("A" & Rows.Count).End(xlUp).Row
                    If lr > 1 Then
                    Range("D2:D" & lr).ClearContents
                    End If
                .AutoFilter
        End With
        
        With Range1
                .AutoFilter 11, "Pieces"
                    lr = Range("A" & Rows.Count).End(xlUp).Row
                    If lr > 1 Then
                    Range("E2:E" & lr).ClearContents
                    End If
                .AutoFilter
        End With
        
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set Rangecopy = ws.Range("F2:J" & lr)
        Rangecopy.Copy
        Range2.PasteSpecial Paste:=xlPasteValues
        
    End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Set Range2 = ws2.Range("A" & lr + 1)

lr = Range("A" & Rows.Count).End(xlUp).Row
        Set Rangecopy = ws.Range("F2:J" & lr)
        Rangecopy.Copy
        Range2.PasteSpecial Paste:=xlPasteValues

Regards
Haree

 

[Alex Blakenburg]

I would start with cleaning up all instances where you have used "Range(" without indicating which sheet it refers to eg
Here you are clearly indicating it refes to sheet ws2

Set Range2 = ws2.Range("A" & lr + 1)

This is unclear, it will refer to the active sheet and I have no idea which sheet is active at this point.
This format has been used in quite a number of lines.
It should either have ws or ws2 in front of it or if it is inside say a With ws / End with group it could be .Range (full stop at the front)

lr = Range("A" & Rows.Count).End(xlUp).Row

 

[Haree]

Hello Alex, Thank you for your time all the lr's where combined with a (With Ws) i defined a separate lr for sheet 2 and now it works.
Thank you

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.