could someone help modify this code so instead of only looking if within Column A is a blank cell it instead looks for example from A to M?

Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long
    
      'Set variables for copy and destination sheets
      Set wsCopy = Workbooks("New-Data.xlsx").Worksheets("Export 2")
      Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
        
      '1. Find last used row in the copy range based on data in column A
      lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
        
      '2. Find first blank row in the destination range based on data in column A
      'Offset property moves down 1 row
      lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
      '3. Copy & Paste Data
      wsCopy.Range("A2:D" & lCopyLastRow).Copy _
        wsDest.Range("A" & lDestLastRow)
        
      'Optional - Select the destination sheet
      wsDest.Activate
      
    End Sub

CodePudding user response：

Find the Last Non-Empty Row of a Range By Using the Find Method

• It should be lDestFirstRow.

'2. Find first empty row in the destination range based on data in columns A:M
Dim lCell As Range: Set lCell = wsDest.Columns("A:M").Find("*", , xlFormulas, , xlByRows, xlPrevious)
If lCell Is Nothing Then lDestLastRow = 1 Else lDestLastRow = lCell.Row   1