Error in last row

Haree

Board Regular
Joined
Sep 22, 2019
Messages
146
Office Version
  1. 2016
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.

VBA Code:
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


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

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

Regards
Haree
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
VBA Code:
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)
VBA Code:
lr = Range("A" & Rows.Count).End(xlUp).Row
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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