Hello,
Before reading this bear in mind I'm an vba rookie!
I currently have an excel workbook called 'MasterRegister', within this workbook is a sheet called 'Register'.
I have another excel workbook called 'RO Status Log - Practice Copy', within this workbook is a sheet called 'R&O Closed'. This sheet is regularly updated with information in the format below: (NOTE: When it updates it adds a row to the top not the bottom)
Column A Column B Column C Column D Column E
<tbody>
</tbody>
As this is updated with new data, I want the excel workbook 'MasterRegister' to also record this same new data in its worksheet "Register" WITHOUT needing to open the 'RO Status Log' workbook.
I want it to record the information in the columns stated below:
R&O Number: Column A
Document Number: Column B
Document Type: Column B
Unit Affected: Column L
Issue/Revision: Column G
So in procedure: I will open the 'MasterRegister' workbook & from a command button on the 'Register' sheet - it will show a message box stating:
"5 new entries have been made in RO Status Log - Entries now recorded in the sheet". If there isn't any new entries it will say "No new entries".
I have attempted to do this - but by pressing the command button it opens the RO Status log workbook which I obviously do not want and attempts to copy the whole column which I do not want also.
Before reading this bear in mind I'm an vba rookie!
I currently have an excel workbook called 'MasterRegister', within this workbook is a sheet called 'Register'.
I have another excel workbook called 'RO Status Log - Practice Copy', within this workbook is a sheet called 'R&O Closed'. This sheet is regularly updated with information in the format below: (NOTE: When it updates it adds a row to the top not the bottom)
Column A Column B Column C Column D Column E
R&O Number | Document Number | Document Type | Unit Affected | Issue/Revision |
15610 | J466 | REPORT | ENGINE | 1 |
17483 | JRRU | LETTER | F18 | 8 |
78172 | HGU7 | SERVICE | 2891 | 10 |
<tbody>
</tbody>
As this is updated with new data, I want the excel workbook 'MasterRegister' to also record this same new data in its worksheet "Register" WITHOUT needing to open the 'RO Status Log' workbook.
I want it to record the information in the columns stated below:
R&O Number: Column A
Document Number: Column B
Document Type: Column B
Unit Affected: Column L
Issue/Revision: Column G
So in procedure: I will open the 'MasterRegister' workbook & from a command button on the 'Register' sheet - it will show a message box stating:
"5 new entries have been made in RO Status Log - Entries now recorded in the sheet". If there isn't any new entries it will say "No new entries".
I have attempted to do this - but by pressing the command button it opens the RO Status log workbook which I obviously do not want and attempts to copy the whole column which I do not want also.
Code:
Sub AutoCopyVersion()
Dim pasteTo As Range
Dim countRows, i As Long
countRows = Application.CountA(Range("A:A"))
Workbooks.Open Filename:="C:\Users\SAN1011\Documents\RO Status Log - Practice Copy.xlsm"
i = Application.CountA(ActiveWorkbook.Sheets("R&O Closed").Range("A:A"))
If i = countRows Then Exit Sub
ActiveWorkbook.Sheets("R&O Closed").Range("A" & countRows + 1 & ":C" & i).Select
Selection.Copy
Workbooks("RO Status Log - Practice Copy.xlsm").Close
Set pasteTo = Sheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.ActiveSheet.Paste Destination:=pasteTo
Application.CutCopyMode = False
End Sub