Copy range with last row to another workbook

Jerryk7946

New Member
Joined
Mar 23, 2019
Messages
3
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
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

New Member
Joined
Mar 23, 2019
Messages
3
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
Try:
Code:
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

New Member
Joined
Mar 23, 2019
Messages
3
Thanks so much. I will comment the lines that control the copy range, destination cell and worksheet names of the source and
target.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,846
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top