Hello,

I currently have 2 excel workbooks.

One called "NewMasterRegister (PRACTICE)" and one called "RO Status Log - Practice Copy".

Within the MasterRegister workbook is a worksheet called 'Register' and within the RO Status Log workbook is a worksheet called 'R&O Closed'.

R&O Closed worksheet is regularly updated with information. The information is updated in reverse order. With new data being entered from row 4.

R&O Closed worksheet has the following format:

Column A: R&O Number

Column B: Document Number

Column C: Document Type

Column D: Unit Affected

Column E: Issue/Revision

I currently have a VBA code which pulls data from the RO Status Log and inserts it into the 'Register' worksheet, entering it into specific columns. However I have 2 problems which I can't figure out:

Here is the code first of all:

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
Problem 1:

'R&O Closed' worksheet in 'RO Status Log - Practice Copy' is constantly being updated with new data inserting from row 4. Now the code imports the data to "MasterRegister" workbook, however it doesn't import all the latest entries - reading from new entry of rows.

As you can see from the code, 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 'R&O Closed' worksheet.

(Maybe programming the code to pull data from according to last saved/updated on R&O Closed worksheet). If no new updates it will not import.

Problem 2:

When it runs it states "4 new entries found in range of A4:E4". This I do not want. This problem links back to problem 1. What I want it to do is read from column A R&O Numbers in "RO Status Log - Practice Copy" and read the R&O numbers corresponding to the new entries and state "4 new entries, numbers: 154768, 1575, do you wish to import?"

Any help will be much appreciated Note: I am quite new to VBA

Thanks a lot!