Friday, 18 January 2013

How To Update Main Workbook Data Sheets With Respective Sheets Data of Sub Workbook

Excel VBA Macro To Update Main Workbook Data Sheets With Respective Sheets Data of Sub Workbook.
'How does this Macros Works:
'Suppose: -
'The Main[This Workbook]workbook has the following Sheets In Some Jumble Order -
'Rawdata,SalesbyCountry,Sheet1,Sheet3,Sheet2,SalebyCustomer,PivotData,,,Sheet4,
'The Sub[Selected Workbook]workbook has the following Sheets In Some Jumble Order -
'Rawdata,Sheet1,PivotData,Sheet2.
'Now the Macro will update the data in Worksheets of the Main workbook which are Match 'with the Names of the Subworkbook.
'i.e., The Sheets-'Rawdata,Sheet1,PivotData,Sheet2' of Main workbook only will update.

Sub UpdateRespectiveSheets()
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")
Workbooks.Open (MyFile)
MyFileName = ActiveWorkbook.Name

For Each Ws In ActiveWorkbook.Sheets
Ws.Activate
For SheetIndex = 1 To ThisWorkbook.Sheets.Count
If ActiveSheet.Name = ThisWorkbook.Sheets(SheetIndex).Name Then
ActiveWorkbook.ActiveSheet.Cells.copy
ThisWorkbook.Sheets(SheetIndex).Range("A1").PasteSpecial
End If
Next SheetIndex
Next Ws

Workbooks(MyFileName).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox "Success Fully Updated Data From Respecitve Sheets", vbInformation, "Success !!!"

Exit Sub
TPR:
MsgBox "Macro Failed/File Selection Cancelled", vbOKCancel, "FailedCancelled !!!"
MsgBox Err.Description 
End Sub

Ecofriendly.,
        Tamatam
                   MBA[Finance]                        

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts