VBA code modification

[ionelz]

Hi,
I have this code for Copy and Paste. I want a modification on bolded line
I want to COUNT last row with data in FileSource (from where I Copy) and replace 100000 with that number
I want to count in Column A, not in entire sheet


Dim FileFolder As FileDialog
Dim FileName As String
Dim FileChosen As Integer
Dim FileSource As Workbook
Dim FileDestination As Worksheet

Set FileDestination = ActiveSheet

Range("B4:F100000").ClearContents
Range("B2:F2").ClearContents

Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
Application.DisplayAlerts = False
FileFolder.Title = "Please Select a Folder and File"
FileChosen = FileFolder.Show
FileName = FileFolder.SelectedItems(1)
Set FileSource = Workbooks.Open(FileName)

Sheets("Sheet1").Range("A2:E100000").Copy

FileDestination.Range("B4").PasteSpecial xlPasteValues
FileDestination.Range("B2").Value = FileName

FileSource.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Tom.Jones]

Replace:
Sheets("Sheet1").Range("A2:E100000").Copy

with
lastRow = Range("A100000").End(xlUp).Row
Sheets("Sheet1").Range("A2:E" & lastRow).Copy

Bafta.