Excel VBA : Import Data from Another Worksheet

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

3. Then press F5 key to run this code, and choose the workbook that you want to insert its data into current workbook :

Sub Import()
 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-13", "*.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:="A1", Type:=8)
 wkbCrntWorkBook.Activate
 Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
 rngSourceRange.Copy rngDestination
 rngDestination.CurrentRegion.EntireColumn.AutoFit
 wkbSourceBook.Close False
 End If
 End With
End Sub

 

Share this post

Post Comment

sixteen + 14 =