Trying to find the blank row after the last row, but my copy won't paste in Excel Macro VBA

Question

so my code for this macro doesn't seem to be working when I use a steady range for the paste function, it seems to be working fine (ex. A1 instead of Range("A" & lDestLastRow)). Essentially, I'm just having this button wherein I open another excel file. Copy the files from there and post certain columns onto this excel file. But wanted to paste it onto the next empty row. Hope someone can help this beginner out. :(

Sub get_data_from_file()
    Dim FileToOpen as Variant
    Dim OpenBook As Workbook
    Dim wsdest as Worksheet
    Dim lDestLastRow as Long

    Application.ScreenUpdating=False
    Set wsdest = Workbooks("Test.xslm").Worksheets("Input Sheet")
    lDestLastRow=Sheet1.Cells(1,1).CurrentRegion.Row+1

    FileToOpen=Application.GetOpenFileName(Title:="Browse for your file and input range",
    FileFilter:"Excel Files (*xls*),*xls*")
    If FileToOpen<>False Then

             SetOpenBook=Application.Workbooks.Open(FileToOpen)
             OpenBook.Sheets(1)Range(B:E).Copy
             wsdest.Range("A", lDestLastRow).PasteSpecial xlPasteValues
             OpenBook.Sheets(1)Range(G:H).Copy
             wsdest.Range("E", lDestLastRow).PasteSpecial xlPasteValues
             OpenBook.Sheets(1)Range(M:M).Copy
             wsdest.Range("G", lDestLastRow).PasteSpecial xlPasteValues

    End if
    Application.ScreenUpdating=True
End Sub

question from:https://stackoverflow.com/questions/65923826/trying-to-find-the-blank-row-after-the-last-row-but-my-copy-wont-paste-in-exce

Answer 1

1. This lDestLastRow=Sheet1.Cells(1,1).CurrentRegion.Row+1 does not find the very last row of the entire sheet. Therefore you should use wsDest.UsedRange.Find("*", , , , xlByRows, xlPrevious).Row

2. Don't name your variable lDestLastRow and then put the row number of the last row + 1 that is very confusing. Name your variable according to what is in there eg lDestNextFreeRow

3. Fix your typos in the code .Sheets(1)Range(B:E) should be .Sheets(1).Range("B:E") and so on.

4. If you copy full columns Range("B:E") you can only past them in full columns otherwise you exceed the maximum rows of the sheet. Therefore limit the rows to your actual data instead of copying full columns.

   Range("B1:E1").Resize(RowSize:=lSrcLastRow)
   

   This will use the first cells of column B to E and then resize to the amount of rows where data was found.

Something like the following should work:

Option Explicit

Public Sub get_data_from_file()
    Dim wsDest As Worksheet
    Set wsDest = Workbooks("Test.xslm").Worksheets("Input Sheet")
    
    Dim lDestNextFreeRow As Long 'find next free row in destination sheet
    lDestNextFreeRow = wsDest.UsedRange.Find("*", , , , xlByRows, xlPrevious).Row + 1
    
    Dim FileToOpen As Variant
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your file and input range", FileFilter:="Excel Files (*xls*),*xls*")
    
    If FileToOpen <> False Then
        Dim OpenBook As Workbook
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        
        Dim lSrcLastRow As Long 'find last used row in the source sheet
        lSrcLastRow = OpenBook.Sheets(1).UsedRange.Find("*", , , , xlByRows, xlPrevious).Row
        
        OpenBook.Sheets(1).Range("B1:E1").Resize(RowSize:=lSrcLastRow).Copy
        wsDest.Range("A" & lDestNextFreeRow).PasteSpecial xlPasteValues
        
        OpenBook.Sheets(1).Range("G1:H1").Resize(RowSize:=lSrcLastRow).Copy
        wsDest.Range("E" & lDestNextFreeRow).PasteSpecial xlPasteValues
        
        OpenBook.Sheets(1).Range("M1").Resize(RowSize:=lSrcLastRow).Copy
        wsDest.Range("G" & lDestNextFreeRow).PasteSpecial xlPasteValues
    End If
End Sub