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!
Bookmarks