Aidworker17
New Member
Dear All,
I have been updating someone else's Excel workbook that has a number of worksheets.
The first worksheet is where the data is entered, and all the main action happens. Though the other worksheets do have important functions that relate to the first worksheet
In the first worksheet :
1. When I try and insert a row, I find that the option to "Insert" is unavailable (greyed out).
2. When I try to cut and insert rows, I am able to cut them, I am able to select "Insert Cut Cells" but when I chose this option I get the a message in a window that says "This Operation is not allowed. The operation is attempting to shift cells in a table on your worksheet".
3. When I try to copy and insert rows, I am able to copy them, I am able to select "Insert Copied Cells" but when I chose this option I get a message in a window that says "This Operation is not allowed. The operation is attempting to shift cells in a table on your worksheet" .
These issues remain whether the worksheet is protected or not. The other worksheets in the workbook do not have this problem.
I would like to undo this so that I can insert, cut & insert cut rows, and copy and insert rows.
When I go to the Table Tools Design tab, I see that there is a table called "Table20". Also, when I do a Ctrl-A, it highlights the first 3 columns only; and requires another Cntrl-A to highlight the rest of the worksheet. Could the table be in the first 3 columns ? Could removing the table help fix the problem ?
Table Update : I just manged to convert the table to a range. The same problems occur except that I can select the "Insert" option but when I select it or the "Insert Cut Cells" or the "Insert Copied Cells", I get a message in a window that says
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CNRL+End to locate the last nonblank cell on the worlsheet. Delete this cell and all cells between it and the last row and column of your data then save".
The file is a database of beneficiaries in an NGO in Africa. As such, I am unable to send you a copy of the file at the moment - due to privacy issues.
I have not created any code or done anything to solve this problem.
Below is some additional information, to give you an idea of my computing experience - to help you to explain to me how to solve the problem .
I am reasonably experienced with Excel, and I am aware of Visual Basic - though I know next to nothing about using it. Even though I don't know the actual language, I can copy and past code if directed to do so. I graduated as a programmer in 1985 (Pascal was all the rage then).
Kind regards,
Jon
I have been updating someone else's Excel workbook that has a number of worksheets.
The first worksheet is where the data is entered, and all the main action happens. Though the other worksheets do have important functions that relate to the first worksheet
In the first worksheet :
1. When I try and insert a row, I find that the option to "Insert" is unavailable (greyed out).
2. When I try to cut and insert rows, I am able to cut them, I am able to select "Insert Cut Cells" but when I chose this option I get the a message in a window that says "This Operation is not allowed. The operation is attempting to shift cells in a table on your worksheet".
3. When I try to copy and insert rows, I am able to copy them, I am able to select "Insert Copied Cells" but when I chose this option I get a message in a window that says "This Operation is not allowed. The operation is attempting to shift cells in a table on your worksheet" .
These issues remain whether the worksheet is protected or not. The other worksheets in the workbook do not have this problem.
I would like to undo this so that I can insert, cut & insert cut rows, and copy and insert rows.
When I go to the Table Tools Design tab, I see that there is a table called "Table20". Also, when I do a Ctrl-A, it highlights the first 3 columns only; and requires another Cntrl-A to highlight the rest of the worksheet. Could the table be in the first 3 columns ? Could removing the table help fix the problem ?
Table Update : I just manged to convert the table to a range. The same problems occur except that I can select the "Insert" option but when I select it or the "Insert Cut Cells" or the "Insert Copied Cells", I get a message in a window that says
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CNRL+End to locate the last nonblank cell on the worlsheet. Delete this cell and all cells between it and the last row and column of your data then save".
The file is a database of beneficiaries in an NGO in Africa. As such, I am unable to send you a copy of the file at the moment - due to privacy issues.
I have not created any code or done anything to solve this problem.
Below is some additional information, to give you an idea of my computing experience - to help you to explain to me how to solve the problem .
I am reasonably experienced with Excel, and I am aware of Visual Basic - though I know next to nothing about using it. Even though I don't know the actual language, I can copy and past code if directed to do so. I graduated as a programmer in 1985 (Pascal was all the rage then).
Kind regards,
Jon
Last edited: