Wednesday, January 16, 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 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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog