Wednesday, 16 January 2013

Excel VBA Macro To Open a Dialog Box to Select a File and Copy All Sheets Into Main WorkBook

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 TPR:

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
TPR:
'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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts