Copy Range Until Last Cell With Data

[sharc316]

Hi, I have some code below that consolidates my workbooks and worksheets. The line below in bold (myWS.Range("A2:Z100").Copy) selects a range from each worksheet and pastes it in the master file. I would like this to copy until the last cell of data since the rows are different with each file. Currently it's defined at row 100.

Any help with be apprecited. Thank you.

Option Explicit

Sub ConsolidateDataKarpExprs()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As String
Dim SumTemplate As String
Dim myWS As Worksheet
Dim sumWS As Worksheet
Dim Last_Row As Long

'Define folders and filenames
MyPath = "C:\Users\Kate\Desktop\FedEx Reporting\Karpathian Express Test\"
SumPath = "C:\Users\Kate\Desktop\FedEx Reporting\"
MyTemplate = "*.xls"  'Set the template.
SumTemplate = "Karpathian Express Master.xlsm"

'Open the template file and get the Worksheet to put the data into
SumName = Dir(SumPath & SumTemplate)
Workbooks.Open SumPath & SumName

Set sumWS = ActiveWorkbook.Worksheets("Linehaul Trips")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
Do While MyName <> ""
    'Open the source file and get the worksheet with the data we want.
    Workbooks.Open MyPath & MyName
    Set myWS = ActiveWorkbook.Worksheets("Linehaul Trips")
    'Copy the data from the source and paste at the end of Summary sheet
    myWS.Range("A2:Z100").Copy
    sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    'Close the current sourcefile and get the next
    Workbooks(MyName).Close SaveChanges:=False        'close
    MyName = Dir                    'Get next file
Loop

Set sumWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
Do While MyName <> ""
    'Open the source file and get the worksheet with the data we want.
    Workbooks.Open MyPath & MyName
    Set myWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
    'Copy the data from the source and paste at the end of Summary sheet
    myWS.Range("A2:Z106").Copy
    sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    'Close the current sourcefile and get the next
    Workbooks(MyName).Close SaveChanges:=False        'close
    MyName = Dir                    'Get next file
Loop



'Now all sourcefiles are copied into the Template file. Close and save it
'Workbooks(SumName).Close SaveChanges:=True
End Sub