Wants to edit vba code

Hi @Waseem Ahmad ,

Thanks for reaching out.

In your case, you can apply protection automatically when each new sheet is created and when the file is exported.

After the data is copied into the new sheet (DSh), you can protect it immediately by adding something like this:

DSh.Rows(1).AutoFilter

DSh.Protect Password:="YourPassword", _
AllowFiltering:=True, _
AllowSorting:=True

This allows users to:

• use filters
• navigate using arrow keys
• view the data

At the same time, it prevents editing cells or modifying the sheet.

More details about worksheet protection options are available here: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect

Only (filter command & left right up down arrow) command should be enabled in all sheets

You just need to enable AutoFilter first, then protect the sheet while allowing filtering.

DSh.Rows(1).AutoFilter
DSh.Protect Password:="YourPassword", AllowFiltering:=True

You can also apply a password automatically when saving each exported workbook.

Replace your current SaveAs line with something like this:

ActiveWorkbook.SaveAs _
Filename:=ThisWorkbook.Path & "\" & ActiveSheet.Name & ".xlsb", _
FileFormat:=xlExcel12, _
Password:="YourPassword"

More information about the SaveAs parameters is available here: https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

One thing to keep in mind is that sheet protection in Microsoft Excel mainly prevents editing rather than fully securing the data. In some cases users may still be able to copy visible cells.

Also, please treat the code snippets above as reference examples and adjust them as needed to fit your workbook or project setup.

Hope this helps! If my answer was helpful, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.

above code is not meeting my needs

Use worksheet and workbook protection in VBA so that each split sheet/file is protected automatically, instead of doing it manually.

Below is an example of how to integrate protection into the existing logic:

Sub SplitDataBase2()
    Dim C As Range
    Dim DSh As Worksheet
    Dim ASh As Worksheet
    Dim strName As String
    Dim rngC As Range
    Dim rngK As Range
    Dim lngKC As Long
    Dim newShName As String
    Dim pwd As String

    ' Password used for all new sheets / files
    pwd = "YourPasswordHere"   ' change as needed

    Set ASh = ActiveSheet

    ' Unprotect master (if needed)
    On Error Resume Next
    ASh.Unprotect Password:=pwd
    On Error GoTo 0

    Set rngC = Application.InputBox("Select a cell in data set", Type:=8)
    Set rngC = rngC.CurrentRegion
    Set rngK = Application.InputBox("Select a cell in the key column of the data set", Type:=8)
    lngKC = rngK.Column

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    With ASh
        Intersect(rngC, rngK.EntireColumn).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=.Cells(.Rows.Count, lngKC).End(xlUp)(3), _
            Unique:=True

        With .Cells(.Rows.Count, lngKC).End(xlUp).CurrentRegion
            For Each C In .Cells.Offset(1).Resize(.Cells.Count - 1, 1)
                If C.Value <> "" Then
                    newShName = Left(C.Value, 31)

                    On Error Resume Next
                    Worksheets(newShName).Delete
                    On Error GoTo 0

                    Set DSh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                    DSh.Name = newShName

                    rngC.AutoFilter _
                        Field:=lngKC - rngC.Cells(1, 1).Column + 1, _
                        Criteria1:=C.Value

                    rngC.SpecialCells(xlCellTypeVisible).Copy DSh.Range("A1")
                    rngC.AutoFilter
                    DSh.Cells.EntireColumn.AutoFit

                    ' Protect the new sheet so only filtering and navigation are allowed
                    '  - AllowFiltering:=True enables AutoFilter
                    '  - All other actions are blocked by default when cells are locked
                    DSh.Protect Password:=pwd, _
                                AllowFiltering:=True, _
                                AllowSorting:=False, _
                                AllowFormattingCells:=False, _
                                AllowFormattingColumns:=False, _
                                AllowFormattingRows:=False, _
                                AllowInsertingRows:=False, _
                                AllowInsertingColumns:=False, _
                                AllowDeletingRows:=False, _
                                AllowDeletingColumns:=False, _
                                AllowUsingPivotTables:=False

                    ' Ensure the filter dropdowns work on a protected sheet
                    If DSh.AutoFilterMode Then
                        DSh.EnableAutoFilter = True
                    End If
                End If
            Next C
            .Clear
        End With
    End With

    If MsgBox("Export the new sheets to files?", vbYesNo) = vbYes Then
        For Each DSh In ActiveWorkbook.Worksheets
            If DSh.Name <> ASh.Name Then
                DSh.Move

                ' Protect workbook structure and sharing with same password
                ActiveWorkbook.Protect Password:=pwd, Structure:=True, Windows:=False

                ' Optional: protect for shared use with passwords
                'ActiveWorkbook.ProtectSharing Password:=pwd, _
                '                               SharingPassword:=pwd

                ActiveWorkbook.SaveAs _
                    ThisWorkbook.Path & "\" & ActiveSheet.Name & ".xlsb", _
                    FileFormat:=xlExcel12

                ActiveWorkbook.Close
            End If
        Next DSh
    End If

    ' Re-protect master sheet if needed
    On Error Resume Next
    ASh.Protect Password:=pwd
    On Error GoTo 0

    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Key points:

1. Master sheet protection
   • Unprotect the master sheet at the start (ASh.Unprotect Password:=pwd) so the code can read/filter.
   • Re-protect it at the end (ASh.Protect Password:=pwd).
2. Per-sheet protection
   • After copying filtered data to each new sheet, call DSh.Protect with AllowFiltering:=True and all other Allow* options set to False.
   • With sheet protection on and cells locked, users can only:
     • Use AutoFilter (filter command).
     • Navigate with arrow keys (up/down/left/right).
   • They cannot edit, insert, delete, or format.
3. Per-file protection
   • Before saving each split workbook, call ActiveWorkbook.Protect Password:=pwd, Structure:=True so sheets cannot be added/removed/renamed.
   • Optionally use ActiveWorkbook.ProtectSharing if shared protection is required, as shown in the example.
4. Password reuse
   • The same pwd variable is used for:
     • Unprotecting/protecting the master sheet.
     • Protecting each split sheet.
     • Protecting each split workbook.

This approach removes the need to manually unprotect/protect each file and enforces that only filtering and navigation are available on all split sheets.

References:

• Protection and security in Excel
• Workbook.ProtectSharing method (Excel)
• Programmatically protect worksheets