Paste Values of the clipboard into a table within a protected worksheet

eddieperu

New Member
Joined
Jul 12, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I've used this excellent code, which inserts rows into a table within a protected worksheet.
I want to make an amendment so instead of just inserting rows, it pastes the values of whatever is already on the clipboard. It still needs to insert these values (ie create the numbers of rows necessary to accommodate the data which is already in the selection)

VBA Code:
Sub AddTableRows()
'PURPOSE: Add table row based on user's selection
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim rng As Range
Dim InsertRows As Long
Dim StartRow As Long
Dim InsideTable As Boolean
Dim RowToBottom As Boolean
Dim ReProtect As Boolean
Dim Password As String
Dim area As Range

'Optimize Code
  Application.ScreenUpdating = False

'What is the worksheet password?
  Password = ""

'Set Range Variable
  On Error GoTo InvalidSelection
    Set rng = Selection
  On Error GoTo 0

'Unprotect Worksheet
  With ActiveSheet
    If .ProtectContents Or .ProtectDrawingObjects Or .ProtectScenarios Then
      On Error GoTo InvalidPassword
      .Unprotect Password
      ReProtect = True
      On Error GoTo 0
    End If
  End With

'Loop Through each Area in Selection
  For Each area In rng.Areas

    'Is selected Cell within a table?
      InsideTable = IsCellInTable(area.Cells(1, 1))
      
    'Is selected cell 1 row under a table?
      RowToBottom = IsCellInTable(area.Cells(1, 1).Offset(-1))
    
    'How Many Rows In Selection?
      InsertRows = area.Rows.Count
    
    'Selection Not Within Table?
      If Not InsideTable And Not RowToBottom Then GoTo InvalidSelection
    
    'Add Rows To Table
      If InsideTable Then
      
        'Which Row in Table is selected?
          With area.Cells(1, 1)
            x = .Row
            y = .ListObject.DataBodyRange.Row
            Z = .ListObject.DataBodyRange.Rows.Count
          End With
          
          StartRow = Z - ((y + Z - 1) - x)
          
        'Insert rows based on how many rows are currently selected
          For x = 1 To InsertRows
            area.ListObject.ListRows.Add (StartRow)
          Next x
      ElseIf RowToBottom Then
        For x = 1 To InsertRows
          area.Cells(1, 1).Offset(-1).ListObject.ListRows.Add AlwaysInsert:=True
        Next x
      End If

  Next area

'Protect Worksheet
  If ReProtect = True Then ActiveSheet.Protect Password

Exit Sub

'ERROR HANDLERS
InvalidSelection:
  MsgBox "You must select a cell within or directly below an Excel table"
  If ReProtect = True Then ActiveSheet.Protect Password
  Exit Sub

InvalidPassword:
  MsgBox "Failed to unlock password with the following password: " & Password
  Exit Sub

End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top