Microsoft Excel - Excel automatic update from one workbook to another workbook (update only culumn A,B, D, F

Asked By kumar lama on 15-Jun-14 03:05 AM
I have one workbook in Desktop C:\Users\Samsung\Desktop\Samsung File\Main File.Xls.
i want update some column value in summery file (for ex my summery file is in (
C:\Users\Samsung\Desktop\Samsung File\Summery File.Xls.

i want update from Main file>Main Sheet>column A B D F N K value to Summery File>Summery Sheet>Column A,B,C,D,E,F
i want automatic update when i put some new Row Value in main sheet it need to be automatic update on summery sheet when i open Summery file.

thank in advance for help.
Harry Boughen replied to kumar lama on 16-Jun-14 08:15 AM
Hello kumar lama,
This code entered into the summary workbook open module might do something like what you want.  Not very elegant but put together fairly quickly.

Private Sub Workbook_Open()

Dim wb As Workbook, wbb As Workbook
Dim strSourceWB As String, strTargetWB As String
Dim range1 As String, range2 As String, range3 As String
Dim a As Range, b As Range, c As Range, d As Range, e As Range, f As Range, g As Range

strSourceWB = "C:\Users\Harry\Documents\excel\copy_source.xlsx" 'C:\Users\Samsung\Desktop\Samsung File\Main File.Xls
strTargetWB = "C:\Users\Harry\Documents\excel\copy_test.xlsm" 'C:\Users\Samsung\Desktop\Samsung File\Summery File.Xls
Set wbb = ThisWorkbook

    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    
    'set up the ranges to be copied from source file
    Set a = wb.Sheets("Sheet1").Range(range1, Range(range1).End(xlDown))
    Set b = a.Offset(0, 1)
    Set c = a.Offset(0, 3)
    Set d = a.Offset(0, 5)
    Set e = a.Offset(0, 13)
    Set f = a.Offset(0, 10)
    range2 = Union(a, b, c, d, e).Address
    range3 = f.Address

    If Not wb Is Nothing Then ' opened the workbook
      On Error Resume Next ' ignore errors
      With wb.Worksheets("Sheet1").Range(range2)
        .Copy wbb.Worksheets("Sheet1").Range("A1")
      End With
      With wb.Worksheets("Sheet1").Range(range3)
        .Copy wbb.Worksheets("Sheet1").Range("F1")
      End With
      On Error GoTo 0 ' stop when errors occur
      wb.Close False ' close the source workbook without saving changes
      Set wb = Nothing ' free memory
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
    
End Sub

Regards
Harry