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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,637
Messages
5,838,497
Members
430,551
Latest member
digitalbrolly

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