Friday, January 18, 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.
Scenario :
'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 Label1:

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

Label1:
MsgBox "Macro Failed/File Selection Cancelled", vbOKCancel, "FailedCancelled !!!"
MsgBox Err.Description 
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.