Import one Spread Sheet from another Excel workbook
A customize excel can work on different lists with different location.
Avoid the manual Copy and Paste procedure by creating a macro.
The macro will open a workbook, copy the spreadsheet and paste it in our excel tool. In the end it will close the workbook opened.
This macro is useful when a tool has many users that are not handy with excel. It makes a tool easy to use and decrease the risks of mistakes.
Use Application.FileDialog to copy a single spreadsheet
This macro will open a workbook with one spreadsheet within it. Afterwards, will copy the spreadsheet into the excel tool.
The Application.FileDialog is used because the worker can select a folder location that can be used as predefined location.
Mostly this is used when you have to go trough many folders to get to the final location. It decreases the time lost to find the files.
Option Explicit
Dim i As Long, lr As Long
Dim wb As Workbook, wb1 As Workbook
Dim strFileToOpen As String, strDefaultFolder As String, SelectedFileItem As String
Dim ws As Worksheet
Dim vFile As Variant
Dim fDialog As FileDialog
Sub OpenWorkbookFileDialog()
Set wb = ActiveWorkbook
Set fDialog = Application.FileDialog(msoFileDialogOpen) 'open file dialog box
With fDialog
.Title = "Please select a file to open" 'define the title to apear on the file dialog box
.InitialFileName = "C:\" 'define the initial path of the folder where your file can be. Here you can put the enire location or including the file name.
.Filters.Clear
.Filters.add "Excel files", "*.xlsx" 'filter the type of documents you want to be able to open.because we copy in an excel the filter is set to excel files
If .Show = -1 Then
SelectedFileItem = .SelectedItems(1)
Workbooks.Open (SelectedFileItem)
End If
End With
'Declaring a Variable to hold the file choosen using File DilogBox
Set wb1 = ActiveWorkbook
wb1.Activate
Sheets(1).Select
Cells.Select
Selection.Copy
wb.Activate
Sheets("Copy Original").Select ' name the sheet from your excel where the copy shoud be placed
Cells.Select
ActiveSheet.Paste
wb3.Close savechanges:=False ' close file opened
End Sub
Leave a Reply
Want to join the discussion?Feel free to contribute!