Impossible to insert

Attachment 30407
Hello,

I have an Excel with macros. Everything was working fine for several months and I changed nothing to the code or parameters.
Today I am suddenly unable to insert new rows or columns. I am sure it worked a few days ago, because I used a macro that used the following line and it worked:
Worksheets(ActiveSheet.Name).Cells(5 + CurrentRowsUpdated, 1).EntireRow.Insert

Up to Worksheets(ActiveSheet.Name).Cells(5 + CurrentRowsUpdated, 1).EntireRow, everything works fine (I tested it in the immediate window and with message boxes). But when we get to the "Insert" part, I get the following error:
Run-time error '1004':
Insert method of Range class failed


Following that, I tried to just insert a row or column in the regular way. It doesn't work, but it unhides the last row.
I also tried it on a new, empty tab, it still doesn't work.
If I open another excel however, it works just fine.

I'm at my wits' end, can't figure out why it suddenly acts like that. Can anyone help me?

For those who want to open the Excel, here's how to use it:

1. Open file and accept macros
2. Go to the « Projects » tab et note down your projects
3. Go to the « General » tab
4. Click on « Show days in Timesheet »
5. Click on « Quick Access To Month »
6. Select a month and a year and press « Create Worksheet »
7. Fill in the timesheet

What to do if a new project is used or an old one isn't used anymore?

1. Go to the « Projects » tab
2. Add projects :

To remove a project: Delete line

1. SORT if required
2. Go to tab YYYY_MMMM (for example 2022_October) that you want to update
3. Click on "Update"
4. New projects should automatically appear at the end
5. Deleted projects will be removed if 0 hours are chosen for that month

When you close the excel and open it again, you will be redirected to the "General" tab and can click on the « Go To Today’s tab » button to go to today's date.

Can you post the workbook?

I just added it to my original post, I forgot to add it originally.

You have accumulated vast numbers of conditional formatting (your macros add conditional formatting but never delete any).
Try manually clearing the conditional formatting, then letting the code put conditional formatting back in.
All worked normally here after that.

Quote:

---

Originally Posted by p45cal

You have accumulated vast numbers of conditional formatting (your macros add conditional formatting but never delete any).
Try manually clearing the conditional formatting, then letting the code put conditional formatting back in.
All worked normally here after that.

---

Thank you for your help.
Just to be clear, you mean removing the conditional formatting that is not in the code but leaving the one in the code as is or do you mean that when I put in a conditional formatting in the code I should first remove the conditional formatting that was already there, also in the code?

If you have conditional formatting (CF) which isn't duplicated/created by the code, leave it; there shouldn't be much.
Take a copy of your workbook, manually clear all the CFs from all the sheets; there's loads of it (it would take you ages to examine and delete CFs one by one).Then you can run the Workbook Open event manually to reinstate CF, then look at the new CFs to see if they meet your approval, and there are no CFs missing.
At this point you can also test that you can insert rows etc. and that this has solved the problem you were having.
Then you need to adjust the code which adds the CF so that it first deletes (some?) CF before adding new CF. Record a macro of your clearing (some) CFs and see what the code looks like before you add it to your workbook open event. If there remain sheets which should have CF but don't, you know to be a bit more selective when deleting CFs from your actual workbook.
I haven't examined your code closely, be aware that copying cells which have CF also carries over their CF, so if that's happening as well you may want to copy values only.

Looking at your code, test if this solves your problems:
Add a first line of the Sub ConditionalFormating(Target As Range, j As Integer) sub:
Target.Parent.Cells.FormatConditions.Delete

Quote:

---

Originally Posted by p45cal

Looking at your code, test if this solves your problems:
Add a first line of the Sub ConditionalFormating(Target As Range, j As Integer) sub:
Target.Parent.Cells.FormatConditions.Delete

---

Only doing that doesn't work.
However, removing all previous conditional formatting before updating worked perfectly, thank you!
For now, I have deleted all previous conditional formatting AND added the line you recommended.
Hopefully this will prevent it from happening again, but if it still happens at least I know how to solve it :)

I'll put this thread as "solved".

Quote:

---

Originally Posted by totonicknick

Only doing that doesn't work.

---

It should!
Remember that that sub only runs on the workbook opening, so making that change and not saving then reopening the workbook will not make a difference.
You can, however, run that sub 'manually' by putting the cursor somewhere in that sub in the vba editor, then pressing F5 on the keyboard.

Quote:

---

Originally Posted by totonicknick

but if it still happens at least I know how to solve it :)

---

Excellent!