Macro Fill Down Not working

[spyldbrat]

Hello,

I have the macro below. I have fill down in two columns but for some reason, it is not filling down to last row. It stops at row 71 even though there are 94 rows of data. The # of rows vary week to week so the last row # can't be static. I have pasted the macro below. I am having the problem where the macro references V7 and U7. I added a space in the macro below where the issue lies (it's toward the bottom).

Sub Sum()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim lr1 As Long, lr2 As Long
        Application.ScreenUpdating = False
    Set sh1 = Sheets("Current")
    Set sh2 = Sheets("Summary")
        sh1.Columns("E:S").EntireColumn.Hidden = False
    sh1.Select
    ActiveWorkbook.Worksheets("Current").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Current").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A6"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Current").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        'copy row
    lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
    sh1.Range(sh1.Cells(lr1, "A"), sh1.Cells(lr1, "R")).Copy
    lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
    sh2.Cells(lr2 + 1, "A").PasteSpecial Paste:=xlPasteValues
    'copy formulas
    cols = Array("E", "F", "H", "I", "K", "L", "N", "O", "Q")
    For i = 0 To UBound(cols)
        sh2.Range(cols(i) & lr2).Copy sh2.Range(cols(i) & lr2 + 1)
    Next
    'copy formats
    sh2.Rows(lr2).Copy
    sh2.Rows(lr2 + 1).PasteSpecial Paste:=xlPasteFormats
    'add date
    sh2.Range("A" & lr2 + 1).Value = Date
    '
    sh1.Range("R:R,O:O,L:L,I:I,F:F").EntireColumn.Hidden = True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    sh2.Select
    Range("A1").Select         
    Sheets("Receipts").Select
    Dim UsdRws As Long
    UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-4])"
    Range("e2:e" & UsdRws).FillDown
    Range("E2:E123").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWorkbook.Worksheets("Receipts").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Receipts").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Receipts").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("Current").Select

    

    Range("U7").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-20],Receipts!C[-20]:C[-17],3,FALSE),"" "")"
    Range("u7:u" & UsdRws).FillDown
    Range("V7").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-21],Receipts!C[-21]:C[-18],4,FALSE),"" "")"
    Range("V7").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-21],Receipts!C[-21]:C[-18],4,FALSE),"" "")"
    Range("v7:v" & UsdRws).FillDown


  

    Range("U7:V186").Select
    Columns("Q:Q").Select
    Selection.Copy
    Columns("U:U").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Columns("V:V").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("V:V").Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0"
    Columns("U:V").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("A7").Select
End Sub

 

[igold]

Why don't you put this line of code in the space you created:

MsgBox "The code says the current last row is: " & UsdRws

If that number is not correct then you know you have to add code to get the correct last used row at that point.

 

[spyldbrat]

I am not sure I understand. Should I be adding this at the beginning or end of this section? And, what will this do?

 

[igold]

At the top of the section where you are having your percieved problems...

I am having the problem where the macro references V7 and U7. I added a space in the macro

The will make a Message Box pop up and tell you what row it thinks is the last row. If this row does not match what you know the last row to be, then you will have to add code there to find the correct last row. You could use the same line you used in the top of your code to find the initial value for the variable "UsdRws"

UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I hope you understand what I am saying...

 

[spyldbrat]

Is there a way for to get the macro to work correctly though? PS Love your "I'm a drinker with a coding problem..."

 

[igold]

If the FillDown is working down to row 71 and it should be going to row 94, then the problem is not with the FillDown but rather what line the code is telling it to stop filling at.

Did you try my message box...

 

[spyldbrat]

I THINK adding that second coding UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" worked!!!! Thank you!

 

[igold]

You're welcome. I am glad you got it working. Thanks for the feedback.