below code ...
... prompts the user to open an excel file (source file)
... copies the data from the first worksheet of the source file
... pastes this data into the first worksheet of the currently active workbook (target file)
I would like somebody to tweak this code so that ...
(1) '/// The targetSheet is to be inserted as a separate sheet right at the very beginning.
(2) '/// Everything has to be copied, i.e. values, formulas, column and row widths
(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!
Help is much appreciated. Thank you very much in advance.
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
.ButtonName = "&Open"
.InitialFileName = "C:\MyFile\Data"
.Filters.Add "Excel files (*.xls; *.xlsm; *.xlsx)", "*.xls;*.xlsm;*.xlsx", 1
.Title = "Please Select an input file"
.AllowMultiSelect = False
If Not (Application.fileDialog(msoFileDialogOpen).Show) Then
MsgBox "No File specified!.", vbExclamation, "Cancel has been pressed!"
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