Macro to copy data from a different workbook

Question

I have a workbook (in Excel 2003 format) with data flowing continuously in three sheets. I want to create a macro in a new workbook (Excel 2010) in which all those data in all the three sheets in the previous workbook to get pasted in a single sheet of my new workbook, one after another. I would prefer the macro to open a dialog box to browse the file where the data is actually present. Can anyone help me please?

While searching I found something like given below. But that is not the one I want exactly.

Sub Open_Workbook()
Dim myFile As String 
    myFile = Application.GetOpenFilename _ 
            (Title:="Please choose a file to open", _ 
             FileFilter:="Excel Files .xls (.xls),") 
    If myFile = False Then 
        MsgBox "No file selected.", vbExclamation, "Sorry!" 
        Exit Sub 
    Else 
        Workbooks.Open Filename:=myFile 
    End If 
End Sub

While searching I found something like given below. But that is not the one I want exactly. — praful prabhakar
Sub Open_Workbook() Dim myFile As String myFile = Application.GetOpenFilename _ (Title:="Please choose a file to open", _ FileFilter:="Excel Files .xls (.xls),") If myFile = False Then MsgBox "No file selected.", vbExclamation, "Sorry!" Exit Sub Else Workbooks.Open Filename:=myFile End If End Sub — praful prabhakar
For future reference, you can revise your question with the code, instead of placing in a comment. Once you open the file, you will want to copy the contents. Where are you putting the data specifically, and where from, as in Sheet Names? Also, do you know that the 3 sheets will always be the same? If so, you don't need a dialog box to ask for the file to open. Meaning, if there is something in the name of the workbook, like a date, or something that matches the master book that compiles the data, you can derive the name that way. — peege

Vasily Ivoyzha Vasily Ivoyzha · Accepted Answer · 2015-01-16T10:00:26

I suppose this code will help you

    Sub wb_sheets_combine_into_one()
    Dim sFileName$, UserName$, oWbname$, oWbname2$, sDSheet$ 'String type
    Dim nCountDestination&, nCount&, nCountCol& 'Long type
    Dim oSheet As Excel.Worksheet
    Dim oRange As Range
    Dim oFldialog As FileDialog
    Set oFldialog = Application.FileDialog(msoFileDialogFilePicker)

    With oFldialog
        If .Show = -1 Then
            .Title = "Select File"
            .AllowMultiSelect = False
            sFileName = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    'open source workbook
    Workbooks.Open sFileName:  oWbname = ActiveWorkbook.Name
    UserName = Environ("username")

    Workbooks.Add: ActiveWorkbook.SaveAs Filename:= _
                    "C:\Users\" & UserName & _
                    "\Desktop\Consolidated.xlsx", _
                    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    oWbname2 = ActiveWorkbook.Name
    sDSheet = ActiveSheet.Name
    nCountDestination = 1
    Workbooks(oWbname).Activate
    For Each oSheet In Workbooks(oWbname).Worksheets
        oSheet.Activate
        sDSheet = ActiveSheet.Name
        ActiveSheet.UsedRange.Copy
        For Each oRange In ActiveSheet.UsedRange
            nCountCol = oRange.Column
        Next
        Workbooks(oWbname2).Activate
        Cells(nCountDestination, 1).PasteSpecial xlPasteAll
        nCount = nCountDestination
        For Each oRange In ActiveSheet.UsedRange
            nCountDestination = oRange.Row + 1
        Next
        Range(Cells(nCount, nCountCol + 1), _
        Cells(nCountDestination - 1, nCountCol + 1)).Value = oSheet.Name
        Workbooks(oWbname).Activate
        With ActiveWorkbook.Sheets(sDSheet).Tab
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0
        End With
    Next
    Workbooks(oWbname2).Save: Workbooks(oWbname).Close False
    MsgBox "File with consolidated data from workbook " & Chr(10) & _
            "[ " & oWbname & " ] saved on your desktop!"
End Sub

Macro to copy data from a different workbook

1 Answers