Open workbook, copy data save and close.

[davidl1]

I created a summary worksheet but now I need the data in a new file. This file would get updated each time the original is modified.
I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "O16" And Target.Value <> "" Then
Application.EnableEvents = False
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("O16").Value
Application.EnableEvents = True
End If

If Target.Address(False, False) = "AH2" And Target.Value <> "" Then
Application.EnableEvents = False
Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Range("M22").Value
Application.EnableEvents = True
End If

This works great but I need the code to open a summary workbook, post the data, save it and close.
The original file is never saved.

 

[Datsmart]

This code will open the workbook listed in the code and copy the active worksheet to the Summary worksheet, then save and close that workbook. Adjust Targetpath and TargetWB filename as needed.

Sub Copy2Summary()
Dim SourceWB As String, SourceWS As String
Dim TargetWB As String, TargetWS As String
Dim TargetPath As String

    'Assign variables
    SourceWB = ActiveWorkbook.Name
    SourceWS = ActiveSheet.Name
    TargetPath = "C:\My Documents"
    TargetWB = "TestBook2.xls"
    TargetWS = "Summary"
    
Application.DisplayAlerts = False
    'Open WorkBook
    With Workbooks.Open(TargetPath & "\" & TargetWB)
        'Clear Target Worksheet of old data
        .Sheets(TargetWS).Cells.Clear
        'Copy Source data to Target
        Workbooks(SourceWB).Sheets(SourceWS).Cells.Copy Destination:=Sheets(TargetWS).Range("A1")
        'Save and Close Workbook
        ThisFile = TargetPath & "\" & TargetWB
        .SaveAs Filename:=ThisFile
        .Close
    End With
Application.DisplayAlerts = True
End Sub

As far as opening and copying to the Summary workbook with EVERY change on your worksheet, that could get a little busy. I would suggest using a "Call" in your code when you want to save to the Summary worksheet. Add this line of code where you want that to happen.

Call Copy2Summary

 

[davidl1]

Thanks very much!