Paste active workbook sheet to new workbook but no extra rows

[mssbass]

My code takes the data from the worksheet selected through a filedialog to my current worksheet (Staffing_Open_Report); however, I only have 750 rows in the worksheet selected so it pastes #N/A values from 751-2500. How do I change the code to only paste the number of rows from the selected workbook? The problem is in the 3rd row from bottom: Application.Workbooks("Star Ninja Tool 10.xlsm").Sheets("Staffing_Open_Report").Range("B2:AF2500").Value = ActiveWorkbook.Sheets("Staffing_Open_Report").Range("A2", ActiveCell.SpecialCells(xlLastCell)).Value


Dim template As Object
Dim myfile As Object
Dim wsDest As Worksheet
Dim lDestLastRow As Long

Set wsDest = Workbooks("Star Ninja Tool 10.xlsm").Worksheets("Staffing_Open_Report")
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

wsDest.Range("A3:AF" & lDestLastRow).ClearContents

Set template = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "\\ccx.carecentrix.com\shares\public\TATMonitoring\RoutingPointStaffingReport"
.filters.Clear
.Title = "Staffing Open Report Selection"
If Not .Show Then
MsgBox "No file selected.": Exit Sub
End If
Workbooks.OpenText .SelectedItems(1)
End With

Set myfile = ActiveWorkbook
ActiveWorkbook.Sheets(1).Activate
Application.Workbooks("Star Ninja Tool 10.xlsm").Sheets("Staffing_Open_Report").Range("B2:AF2500").Value = ActiveWorkbook.Sheets("Staffing_Open_Report").Range("A2", ActiveCell.SpecialCells(xlLastCell)).Value

myfile.Close
template.Activate

 

[BobUmlas]

Change the "offending" statement to 2 statements:
n= ActiveCell.SpecialCells(xlLastCell).Row
Application.Workbooks("Star Ninja Tool 10.xlsm").Sheets("Staffing_Open_Report").Range("B2").Resize(n-1).Value = ActiveWorkbook.Sheets("Staffing_Open_Report").Range("A2", ActiveCell.SpecialCells(xlLastCell)).Value

 

[mssbass]

That only brought in column A from the source document. The source document has columns A:AE

 

[BobUmlas]

Oops - sorry - didn't notice that. Change to:
n= ActiveCell.SpecialCells(xlLastCell).Row
Application.Workbooks("Star Ninja Tool 10.xlsm").Sheets("Staffing_Open_Report").Range("B2").Resize(n-1,31).Value = ActiveWorkbook.Sheets("Staffing_Open_Report").Range("A2", ActiveCell.SpecialCells(xlLastCell)).Value

 

[mssbass]

works great! thankyou!!