New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Retrieve Data From Another Worksheet

Free Excel Macros

Retrieve Data From Another Worksheet

Postby Sisyphus » Thu Mar 08, 2012 9:02 pm

This code will pick the data from another workbook and insert it in your currently active worksheet at the spot your specify. You can call this code from a loop to import multiple data:
Code: Select all
Private Sub GetForeignData(ByRef Target As String, _
                           ByVal SourceCell As String, _
                           Optional ByVal SourceSheet As Variant)

    Const SourceBookName As String = "D:\Alpha\Beta\The Other Workbook.xls"
    Dim SourceBook As Workbook
    Dim TargetSheet As Worksheet

    Set TargetSheet = ActiveWorkbook.Sheets("Sheet1")
    ' You will never see that the workbook was opened:
    Application.ScreenUpdating = False
    Set SourceBook = Workbooks.Open(SourceBookName, ReadOnly:=True)

    ' Do your importing of data here, for example:
    ' Sourcesheet can be a number or a name, but if it is nothing ...
    If VarType(SourceSheet) = vbError Then SourceSheet = 1
    TargetSheet.Range(Target).Value = SourceBook _
               .Worksheets(SourceSheet).Range(SourceCell).Value

    ' Close the SourceBook without asking to save changes:
    SourceBook.Close Savechanges:=xlDoNotSaveChanges
    ' Make the changes in your worksheet visible:
    Application.ScreenUpdating = True
End Sub

The other workbook must be closed at the time of calling this code. If it doesn't exist a fatal error will occur.
The workbook to be accessed is specified in the first line of code. Assign any suitable value to the Const SourceBookName. It must be a text string and it must be a full workbook name, including drive and path. The extension can be xlsx or xlsm if you are using Excel 2007 or newer.

The following two procedures show various ways of calling the above procedure. From an environment where the surrounding code was largely created by the macro recorder the methods demonstrated in this example will most likely be useful:
Code: Select all
Private Sub Test_GetForeignData1()

    ' Fully referenced method:
      ' (Sequence of parameters is immaterial)
    GetForeignData SourceSheet:="Sheet2", SourceCell:="B1", Target:="A1"

    ' Implied reference method:
      ' (Sequence of parameters must match sequence of arguments!)
    GetForeignData "A1", "B1", "Sheet2"
   
    ' SourceSheet can be a name or a number
      ' IF the argument is omitted, Sheets(1) will be used
      ' IF the specified sheet doesn't exist a fatal error occurs
   
    ' Using a variable as parameters:
    Range("A1").Select
    GetForeignData Selection.Address, "B1"
    GetForeignData ActiveCell.Address, "B1"
   
    Range("A1:A10").Select
    GetForeignData Selection.Cells(1).Address, "B1"
End Sub

The next example imports the data from 12 adjecent cells in the source workbook into 12 different cells in the active worksheet:
Code: Select all
Private Sub Test_GetForeignData2()

    Dim Target As String
    Dim SourceCell As String
    Dim SourceSheet As Variant
    Dim SourceColumn As String
    Dim R As Long
   
    SourceSheet = "Monthly Report"
    SourceColumn = "AC"
    Range("A1:A12").Select
    For R = 1 To 12
        ' Import from Range("AC1:AC12") :
        SourceCell = Cells(R, SourceColumn).Address
        GetForeignData Selection.Cells(R).Address, SourceCell, SourceSheet
    Next R
End Sub
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 26 guests