Excel: lock sheet, but keep tables expandable

I would like to suggest VBA (Macro) will help you to Copy used range from all Worksheets into New, exactly works as Data Consolidation.

Sub CombineData()

    Dim I As Long
    Dim xRg As Range

    On Error Resume Next

    Worksheets.Add Sheets(1)

    ActiveSheet.Name = "MasterSheet"

   For I = 2 To Sheets.Count
        Set xRg = Sheets(1).UsedRange

        If I > 2 Then
            Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
        End If

        Sheets(I).Activate
        ActiveSheet.UsedRange.Copy xRg
    Next

End Sub

How it works:

• Copy & Paste this code as Module.
• Macro will create new sheet in existing Workbook named as MasterSheet, which is editable.
• In place of copy data from Top Row to last, I've used the UsedRange method, since will accommodate every updates.

It's important when using Worksheet_Change to make reference to the Target range.

I set up some dummy data. J2 was blank. I typed Mango into B2 and stepped into the Sub routine. Note that when we start, inputCell is zero (because J2 is blank).

So, copyRange will be "A0", which is not a valid address. This is not the cause of your problem here, but it could cause problems elsewhere.

We can use Target to check which cell has been edited. So for your situation, you would use something like this:

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    
ElseIf Target.AddressLocal = "$B$2" Then
    'do something when B2 was changed
    
Else
    'do something else, or nothing

End If

Every time your current code calls .EntireRow.Insert, it's causing the Worksheet_Change event to be called again, which is causing another row to be inserted, which is causing the event to be called again, which is causing another row, and so on. In fact, I suspect your code will continue inserting rows like this, causing your data below to seem as if it has vanished. This is also likely causing slowness.

So, you should always refer to Target so that the macro only takes action when you have changed specific cells (and not when you are inserting the row).

One other item you might want to consider is putting putting your password as a constant in this sheet or global constant in another module. If you then don't want users to see that password, you will need to lock the VBA project, as described here. This will help you a little I think as you will only have to type it once in the VBA project, so there are fewer opportunities for typos.

See if this works for you by pasting it in the code behind the worksheet. Option Explicit is usually a good idea as it forces the system to not accept variables unless they are Dim'd

Option Explicit

Const pwd As String = "mehedi"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCell As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim validateCell As String
Dim fruitType As String

'this is just giving a short name for the current sheet
Set ws = ActiveSheet

'set this at the top
fruitType = "Mango"

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    inputCell = Target

    'only take action if the condition is met
    If inputCell >= 5 Then
        copyRange = "A" & inputCell
        
        ws.Unprotect Password:=pwd
        
        ws.Range(copyRange).EntireRow.Insert 'causes this event to fire again, so be careful~
        
        ws.Range(copyRange & ":C" & inputCell).Merge
        ws.Range(copyRange & ":C" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        ws.Range("D" & inputCell & ":F" & inputCell).Merge
        ws.Range("D" & inputCell & ":F" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        'perhaps you need this here as well?
        ws.Protect Password:=pwd
    End If
    
ElseIf Target.AddressLocal = "$B$2" And Target.Value = fruitType Then
    'only take action if the right fruit was entered into the right cell
    
        ws.Unprotect Password:=pwd
        ws.Range("J2").Locked = False
        ws.Protect Password:=pwd


End If

End Sub

EDIT:

The code as listed above works just fine in this series of steps:

1. all cells except B2 are locked
2. enter "Mango" in B2
3. Sheet is unprotected, J2 is unlocked, Sheet is protected
4. enter 15 in J2
5. row is inserted above current row 15, A15:C15 are merged and given a border, D15:F15 are merged and given a border

The only thing I can think at this time is there is some detail about your problem which you haven't stated. In that case, you will need to debug this issue.