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
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.