Excel VBA Macro to Open a File Dialog Box to Select a File and Copy All Sheets Into Target WorkBook
'Macro To Open a Dialog Box to Select a File and Dump All Sheets Into Our Work Book.
'All The Sheets are dumped in to our work book with respective sheet names and data.
'This is a very user friendly and powerful macro.
Option Explicit
Sub OpenDialogCopyFile()
Dim MyFile As Variant
Dim MyFileName As String
Dim Ws As Worksheet
Dim SheetIndex As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo Err1:
SheetIndex = 1
MyFile = Application.GetOpenFilename("Excel Files, *.xls;*.xlsx;*.xlsm")
'If MyFile = False Then Exit Sub
Workbooks.Open (MyFile)
MyFileName = ActiveWorkbook.Name
For Each Ws In ActiveWorkbook.Sheets
Ws.Activate
ActiveSheet.Copy After:=ThisWorkbook.Sheets(SheetIndex)
'Add Each sheet of selected file after first sheet of our file
SheetIndex = SheetIndex + 1
Next Ws
Workbooks(MyFileName).Activate
' Selected File Saving and Closing after Copied
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox "Success Fully Copied Selected Workbook", vbInformation, "Success !!!"
Exit Sub
Err1:
'If you cancel file selection / macro fails to run...then kick you this msgbox
MsgBox "Macro Failed/File Selection Cancelled", vbOKCancel, "Failed/Cancelled !!!"
End Sub
Thanks,TAMATAM
'Macro To Open a Dialog Box to Select a File and Dump All Sheets Into Our Work Book.
'All The Sheets are dumped in to our work book with respective sheet names and data.
'This is a very user friendly and powerful macro.
Option Explicit
Sub OpenDialogCopyFile()
Dim MyFile As Variant
Dim MyFileName As String
Dim Ws As Worksheet
Dim SheetIndex As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo Err1:
SheetIndex = 1
MyFile = Application.GetOpenFilename("Excel Files, *.xls;*.xlsx;*.xlsm")
'If MyFile = False Then Exit Sub
Workbooks.Open (MyFile)
MyFileName = ActiveWorkbook.Name
For Each Ws In ActiveWorkbook.Sheets
Ws.Activate
ActiveSheet.Copy After:=ThisWorkbook.Sheets(SheetIndex)
'Add Each sheet of selected file after first sheet of our file
SheetIndex = SheetIndex + 1
Next Ws
Workbooks(MyFileName).Activate
' Selected File Saving and Closing after Copied
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox "Success Fully Copied Selected Workbook", vbInformation, "Success !!!"
Exit Sub
Err1:
'If you cancel file selection / macro fails to run...then kick you this msgbox
MsgBox "Macro Failed/File Selection Cancelled", vbOKCancel, "Failed/Cancelled !!!"
End Sub
Thanks,TAMATAM
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.