Copy range with last row to another workbook

[Jerryk7946]

I am new to VBA. I found a piece of code that I would like to modify to be more efficient.
I have two workbooks.
Workbook A - Source
Workbook B - Target

The code in Workbook B allows me to find and open Workbook A
and copy the data to Workbook B. The method of selecting the data
is to draw the range via a message box. The range of columns is F
through U is always the same. Cell F4 is always the beginning of the
range. Destination cell A4 in Workbook B is always the same.
The only thing that changes is the number of rows. Since most of
the data is constant with the exception of the row length. I thought
that adding the ability to find the last row would minimize a users
intervention.

I have attached the code for your review.
I am sure a couple of lines of would make this process more
efficient.

Sub ImportDatafromotherworksheet()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange As Range
Dim rngDestination As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2007-16", "*.xlsx; *.xlsm; *.xlsa"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set wkbSourceBook = ActiveWorkbook
Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="F2", Type:=8)
wkbCrntWorkBook.Activate
Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A4", Type:=8)
rngSourceRange.Copy rngDestination
rngDestination.CurrentRegion.EntireColumn.AutoFit
wkbSourceBook.Close False
End If
End With
End Sub

 

[mumps]

What is the full name of Workbook A including extension (xlsx, xlsm)? What is the name of the source sheet and the destination sheet?

 

[Jerryk7946]

Workbook A Name - Staff Trak Tax Engagement Team Members.xlsx
Workbook A Sheet Name - Data

Workbook B Name - Staff Trak Tax Engagement Team Import.xlsm
Workbook B Sheet Name - Data

 

[mumps]

Try:

Sub ImportDatafromotherworksheet()
    Dim srcWS As Worksheet, desWS As Worksheet, lastRow As Long
    Set desWS = ThisWorkbook.Sheets("Data")
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-16", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set srcWS = Sheets("Data")
            lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            srcWS.Range("F4:U" & lastRow).Copy desWS.Range("A4")
            srcWS.Columns.AutoFit
            ActiveWorkbook.Close False
        End If
    End With
End Sub

 

[Jerryk7946]

Thanks so much. I will comment the lines that control the copy range, destination cell and worksheet names of the source and
target.

 

[mumps]

You are very welcome.