We value your feedback.
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.
|INSERT value into first row in 75 workbooks||8||38|
|why PtrSafe before Function on 64bit does not fix the APIs?||2||29|
|my excel file states circular references, how do i find them?||2||22|
|Excel VBA to convert a string into a formula||19||13|
Join the community of 500,000 technology professionals and ask your questions.