Hi All,
I would like to build a sheet that allows users to highlight a row, click a button, and insert a new row of data after that row ( and consequently shift the data below the added row down). The sub I currently have just adds the new data to the bottom of the existing data, but I want the ability to add a row anywhere in the data based on a selected location. For example - I want the user to be able to select row 20:20, press the add row button, have that new row of data inserted as 21:21 while shifting the data below down one row. Any ideas? This would be a huge help!!
<tbody>
</tbody>
Thanks again for all your help! This site never ceases to amaze me!
Best,
Spencer
I would like to build a sheet that allows users to highlight a row, click a button, and insert a new row of data after that row ( and consequently shift the data below the added row down). The sub I currently have just adds the new data to the bottom of the existing data, but I want the ability to add a row anywhere in the data based on a selected location. For example - I want the user to be able to select row 20:20, press the add row button, have that new row of data inserted as 21:21 while shifting the data below down one row. Any ideas? This would be a huge help!!
Sub Checkbook_AddRow() 'Declare local variables------ Dim lo As ListObject Dim ws As Worksheet Dim wsSettings As Worksheet '----------------------------- 'Set worksheet and list object variables-------------- Set wsSettings = ThisWorkbook.Worksheets("Settings") Set ws = ActiveSheet Set lo = ws.ListObjects(1) '----------------------------------------------------- ws.Unprotect wsSettings.Range("Settings_Password") MsgBox "Please highlight the Row you want to insert a line of data below" 'Add new row to table and set all table fields lo.ListRows.Add (lo.ListRows.Count + 1) lo.DataBodyRange(lo.ListRows.Count, 1) = "9999" lo.DataBodyRange(lo.ListRows.Count, 2) = "PENDING" lo.DataBodyRange(lo.ListRows.Count, 3) = "0" lo.DataBodyRange(lo.ListRows.Count, 4) = wsSettings.Range("Settings_FYStartDate") lo.DataBodyRange(lo.ListRows.Count, 5) = "0.001" lo.DataBodyRange(lo.ListRows.Count, 6) = "11111.00" lo.DataBodyRange(lo.ListRows.Count, 7) = "first name" lo.DataBodyRange(lo.ListRows.Count, 8) = "last name" lo.DataBodyRange(lo.ListRows.Count, 9) = "9999999" lo.DataBodyRange(lo.ListRows.Count, 10) = wsSettings.Range("Settings_FYStartDate") lo.DataBodyRange(lo.ListRows.Count, 11) = "Enter notes here………………" lo.DataBodyRange(lo.ListRows.Count, 12) = lo.DataBodyRange(1, 12) ws.Protect wsSettings.Range("Settings_Password"), AllowFiltering:=True End Sub |
<tbody>
</tbody>
Thanks again for all your help! This site never ceases to amaze me!
Best,
Spencer