Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Draw a value from another worksheet, not as static text but as a formula (using VBA)||2||34|
|Excel Named Range||31||44|
|Count How Many Times Each Number Repeats||14||22|
|Array not populating all cells in Excel||6||15|