Replace the previous macro with the code below. This version forces you to enter new data in the first available blank row. It also generates the next Ref number. Because the macro is a worksheet change event, it wouldn't work to have the Master automatically open when data is entered into the "child" worksheet. However, we could open the Master automatically when the "child" workbook is opened. If that works for you, are the child files and the Master file saved in the same folder? If not, what is the full path to the folder where the Master is saved?
Dim lastRow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
lastRow = Columns(1).Find("*", After:=Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:Y")) Is Nothing Then Exit Sub
Dim fnd As Range, desWS As Worksheet
Set desWS = Workbooks("Master.xlsx").Sheets("Sheet1")
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 1 And Target.Row <> lastRow + 1 And Target <> "" Then
Target.ClearContents
Cells(lastRow + 1, 1).Select
Application.ScreenUpdating = True
MsgBox ("You have skipped a row(s)." & Chr(10) & "Please enter the 'Finding Severity' in the selected cell.")
Application.EnableEvents = True
Exit Sub
ElseIf Target.Column = 1 And Target.Row = lastRow + 1 And Target <> "" Then
Range("H" & Target.Row) = Left(Range("H" & Target.Row - 1), 8) & Mid(Range("H" & Target.Row - 1), 9, 9999) + 1
End If
Set fnd = desWS.Range("H:H").Find(Range("H" & Target.Row), LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
desWS.Cells(fnd.Row, Target.Column) = Target
Else
desWS.Cells(desWS.Rows.Count, Target.Column).End(xlUp).Offset(1) = Target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Bookmarks