VBA resize table on a protected sheet

[willow1985]

Hello,

I have been trying to solve a problem with a table that is on a protected sheet.

As I am sure many of you know Tables do not auto expand when the sheet is protected. I do however need the table to expand and have the sheet protected.

Any advise on a work around for this would be greatly appreciated.

Note: One of my ideas was to set off a macro when new data was entered in column C that would unlock the spreadsheet, expand the table and then re-lock the spreadsheet but I cannot seem to make this work/find the right code to expand the table without knowing the exact range.

The data starts at A3 and goes to the last row of data in column W, however there are some blanks periodically in various columns. The only column that does not have any blanks is column C.

The locked formulas are in columns L,M and N, there are other formulas that are not locked however in other columns.

There will also be situations when a macro will dump large blocks of data will be pasted in the last blank row in column A to column G so I don't know if using "new data in column C" as a trigger would work..

I hope someone can help as I feel stumped on this one.

My table name is "MF"

Thank you!

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
    If Target.Value <> "" Then

  
        Worksheets("Master Forecast").Unprotect Password:="2150"

'Code to resize table or bring down data by 1 row??

Worksheets("Master Forecast").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True, Password:="2150"

End If
End Sub

 

[dancer5]

Hello,
I've been trying to solve the same problem with having my table auto resize to include the new rows when data is pasted into columns Q:W below the currently sized table. I tried the code above but am getting the same 1004 error. My table headers are in row 4 and I modified the code to

With Me.ListObjects("table1")
            .Resize .Range(1).CurrentRegion.Offset(1).Resize(.Range(1).CurrentRegion.Rows.Count - 3)
         End With

But no luck.
Another problem I've encountered is that columns A:B need to stay unprotected.

I am new to VBA, so any help would be appreciated!