Be seen. Boost your question’s priority for more expert views and faster solutions
Private Sub Import_Data_Into_Current_Workbook() Dim customerFilename As String Dim customerWorkbook As Workbook Dim targetWorkbook As Workbook ' active workbook is the target Set targetWorkbook = Application.ActiveWorkbook With Application.fileDialog(msoFileDialogOpen) .ButtonName = "&Open" .InitialFileName = "C:\MyFile\Data" .Filters.Clear .Filters.Add "Excel files (*.xls; *.xlsm; *.xlsx)", "*.xls;*.xlsm;*.xlsx", 1 .Title = "Please Select an input file" .AllowMultiSelect = False End With If Not (Application.fileDialog(msoFileDialogOpen).Show) Then MsgBox "No File specified!.", vbExclamation, "Cancel has been pressed!" Exit Sub Else customerFilename = Application.fileDialog(msoFileDialogOpen).SelectedItems(1) Set customerWorkbook = Application.Workbooks.Open(customerFilename) ' copy data from customer to target workbook '/// to be incorporated (1) The targetSheet is to be inserted as a separate sheet right at the very beginning. Dim targetSheet As Worksheet Set targetSheet = targetWorkbook.Worksheets(1) Dim sourceSheet As Worksheet Set sourceSheet = customerWorkbook.Worksheets(1) '/// To be incorporated (2): Everything has to be copied, i.e. values, formulas, column and row widths Intersect(sourceSheet.UsedRange, sourceSheet.UsedRange.Offset(0, 0)).Copy targetSheet.Range("A1") '/// To be incorporated (3): If a sheet with the same name already exists in the target workbook, a msgbox has to say so and the macro is to abort! targetSheet.Name = sourceSheet.Name End If customerWorkbook.Close End Sub
Join the community of 500,000 technology professionals and ask your questions.