Pulling data from regulary updated closed workbook

[sdhutty]

Hi there,

I currently have a excel workbook labelled "MasterRegister" with a worksheet in there called "Register".

I have a vba code whereby it pulls data from a closed workbook called "RO Status Log - Practice Copy" and inserts it into the "Register" spreadsheet. Here is the code:

Option Explicit
Sub AutoCopyVersion()
Dim countRowsThis As Long, countRowsSource As Long, iNewRecords As Integer, strAddress As String, strReport As String, intBtnType As Integer, proceed As Integer
' count rows in this file
countRowsThis = Application.CountA(Range("A4,B4,C4,D4"))
' open source file, which becomes active file
Workbooks.Open Filename:="C:\Users\SAN1011\Documents\RO Status Log - Practice Copy.xlsm"
' count rows in that source file
countRowsSource = Application.CountA(ActiveWorkbook.Sheets("R&O Closed").Range("A4,B4,C4,D4"))
' calculate new entries
iNewRecords = countRowsSource - countRowsThis
' decide what to do based on delta
Select Case iNewRecords
    Case Is < 0
        strReport = "ERROR: there are less entries in source file than in this file"
        intBtnType = vbCritical
        
    Case 0
        strReport = "no entries found in source file"
        intBtnType = vbInformation
        
    Case Else
        
        ' create address for copying
        strAddress = "A" & 4 & ":E" & iNewRecords
        
        ' ask if import required
        proceed = MsgBox(iNewRecords & " new records found at range " & strAddress & ". Do you wish to import data?", vbQuestion + vbYesNo)
        If proceed = vbYes Then
        
        ' copy / paste
           With ActiveWorkbook.Sheets("R&O Closed").Range(strAddress)
           .Columns(1).Copy
           ThisWorkbook.Sheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           .Columns(2).Copy
           ThisWorkbook.Sheets("Register").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           .Columns(3).Copy
           ThisWorkbook.Sheets("Register").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           .Columns(4).Copy
           ThisWorkbook.Sheets("Register").Range("L" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           .Columns(5).Copy
           ThisWorkbook.Sheets("Register").Range("G" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End With
        Application.CutCopyMode = False
            
            strReport = iNewRecords & " new entries found. Data Entered. Copied from range " & strAddress
            intBtnType = vbInformation
        Else
            ' no action required
        End If
        
End Select
' report results and close file
endRoutine:
Workbooks("RO Status Log - Practice Copy.xlsm").Close savechanges:=False
If strReport <> "" Then MsgBox strReport, intBtnType
End Sub

There a few problems which I can't suss out & have been trying for a while:

1)

"RO Status Log - Practice Copy" is constantly being updated with new information. The new information is entered in reverse - so information starts from row 4. Thus if 1 new entry is added, the current row 4 will push down to row 5.

Now it imports the data to "MasterRegister" workbook, however it doesn't import all the latest entries - (I want it to import according to last saved or last updated from R0 Status Log).

I currently have the code to only pull from range A4:E4. I do not know how to program the code to pull all the latest data from different rows in the sheet.

2)

When it runs it states "4 new entries found in range of A4:E4". This I do not want. What I want it to do is read from column A in "RO Status Log - Practice Copy" and read the numbers corresponding to the new entries and state "4 new entries, numbers: 154768, 1575" etc.


Any help would be appreciated!

Thank you.