Error “A table can’t overlap another table” when creating a new sheet

[annica123]

I have a really strange problem that’s been bothering me for some time now. I have this planning workbook, it’s for a small stab – they want to plan their time schedule for a year ahead for every co-worker. The workbook is built on a couple of sheets, Template, calendar and so on - most of them are hidden. At the start-sheet there are this button “Ny medarbetare” who is the button to create a new sheet for every co-worker. First you come to a form where it’s necessary to fill every textbox and then you push on the button “Skapa” to create the new sheet. And here is where the problem starts and ONLY if there are no other sheets for co-workers. If there are other co-worker sheets it’s seems to be working fine (seems maybe is the keyword though it’s has’nt been testing yet). But if there is no other co-worker sheets yet, I get this error “A table cant overlap another table” and then its creating a Template(2) out of an existing sheet (and just rename it) and a co-workers sheet that is only half created and without the template.

I’m not that experienced with VBA so this is really bothering me. Adding some pictures of the described errors and the code that is creating the new sheet.

Please Login or Register  to view this content.

Hope anyone has any ideas.

[Paul]

Can't say I know what the issue is, but a few things:

1. Why are you using a Private Function to do work, instead of a Sub (Public or Private)? While they both can do work, functions are usually used to perform calculations and return a value to another function or sub (or custom worksheet function).

2. You Dim a 'newSheetName' but never use it in the code.

3. Generally you can use ThisWorkbook.Worksheets.Count. Not much need to create a variable to count/store the number of worksheets. Also, use Long instead of Integer. Excel just converts Int's to Long anyway. See here.

[annica123]

Thanks for the answer!

I'm not the one who wrote this code, but I'm guessing the function is there because the return value (the sheet) needs some more work to be done after the sheet is created. It's in use in the method that is calling the function.

The other suggestions didn't change the functions behavior, sad to say.. but all suggestions is very appreciated so thank you very much.