Self Populate from Excel FILE to FILE

Hi Lois,

I have not tested the following code snippets, but you (or someone with VBA skills) should be able to use these as "building blocks" to achieve what you want.

Both workbooks need to be open to copy data from one to the other. Also, both workbooks should be saved in the " .xlsm " file format. Your finished macro needs to be placed in a module in the Soucre workbook(s).

Step one is to declare the variables:

' declare variables
Dim wb As Workbook
Dim SumFilePath As String
SumFilePath = "C:\Excel\Summary.xlsm"
Dim DataToCopy As Variant
Dim SrcDataRow As Long
Dim SrcDataCol As Long
Dim LastDataRow As Long
Dim NewDataRow As Long
Dim NewDataCol As Long
Dim ReturnVal As String

Step two is to check if the Summary workbook is open:

' check all currently open workbooks
For Each wb In Workbooks
If wb.Name = "Summary.xlsm" Then
    ReturnVal = "fileopen"
End If
Next

If ReturnVal <> "fileopen" Then
Workbooks.Open (SumFilePath)
End If

Step three is to use a loop and copy the data from Source to Summary

    ' find last row with data in source file
LastDataRow = Range("B" & Rows.Count).End(xlUp).Row
SrcDataRow = 3   ' first row of data to copy
SrcDataCol = 2   ' column "B" with source data
    ' find last row with data in Summary file
NewDataRow = Summary.xlsm.Range("A" & Rows.Count).End(xlUp).Row + 1
NewDataCol = 1   ' first column to copy data to


Do Until SrcDataRow > LastDataRow
    DataToCopy = Cells(SrcDataRow, SrcDataCol).Value
    SumFilePath.Cells(NewDataRow, NewDataCol) = DataToCopy

    SrcDataRow = SrcDataRow + 2
    NewDataCol = NewDataCol + 1
Loop

Step four is to save the changes to the Summary workbook and then close it:

' save changes to Summary workbook and close file
SumFilePath.Close True

Hope this helps; if so please mark the answer as selected.

Cheers   :-)