Need help deleting sheets and moving existing sheets down in order

**MAGICofSeth** · 11-23-2012, 09:28 PM

Hello,

I have really coded myself into a corner and need help getting out. The first issue is that my spreadsheet is too large to post here, so I will post screenshots and code segments and anything else I can to help describe the issue.

The MAIN issue is that once the spreadsheet is set up and in use, if a sheet has to be deleted it destroys the whole spreadsheet. To clarify, amongst other sheets are 25 sheets in a row that contain the data of 25 people being logged. It looks like this:

Attachment 195916

Everything works perfect until someone leaves your department or is fired. If you delete their sheet, it messes up how several of the other sheets display their data. Here are some images to see why:

Attachment 195917

Attachment 195918

Here is some important info: I do have VBA already coded to HIDE sheets if there are less than 25 people being tracked. The code looks like this:

Please Login or Register  to view this content.

The end result is that when you initially set up the spreadsheet with 25 or 20 or 18 people, whatever number, you simply need to put that number of people in the box on the first page and the spreadsheet only shows information and calculates for that number of people.

Now- once you’ve been using this spreadsheet for a while and someone you have been logging moves to another department or is separated- THERE IS NO EASY WAY TO GET RID OF THEIR SHEET! If you hide the sheet manually, the data still shows up on all the other summary sheets. If you delete the sheet is throws errors into everything.What does work is as follows:

The only that works is a laborious process involving renaming every sheet after the one you want to remove, and then overwriting each sheet with the sheet after its data and renaming it accordingly. That sounds very confusing but I will try to explain better. If you have the sheets named: A, B, C, D, E, and F but no longer need to log for sheet B- the current process is to rename sheet C to C., D to D., E to E., and F to F. THEN copy the data from C over Sheet B (the one no longer needed) and rename sheet B to C (which is why you had to first rename C to C.). Then you have to copy sheet D and paste it over the data in sheet C and rename sheet C to D. Then you have to copy the data from sheet E over the… etc. Good grief! It takes forever.

So what happens at the end now that you have a sheet you were using but now don’t need due to the domino effect. That non needed sheet at the end is named with number. If you had 25 people (a full spreadsheet) and now have one not needed sheet at the end, that sheet would become named 25 and be hidden when the cell on the first page is set to show only 24 people now. If you start with 22 people sheets and are getting rid of one, the now not needed sheet at the end would need to be named 22 and would be hidden behind the sheets already hidden and named 23, 24, and 25.

Here is I think what I need- but perhaps there is a much better idea out there:

I need some VBA Code (which this is above my skill level) that if a user enters the current name of the sheet to be removed, the VBA goes through the above process of renaming all the sheets after the one entered, copies the data from the sheet after to the sheet before for every sheet after the entered one, and then renames all the sheets to correctly correspond with their new data, and lastly for the new not needed sheet at the end name it whatever number from 25 it now actually is.

The above paragraph is really what I’m after. I am happy to send any further detail, code, screenshots, and whatever I can to help out with this. The spreadsheet is too large to post on this site but I do have a dropbox account I think I can put it on if that is allowed.

I could truly use your help. Thank you for reading this ridiculously long post and your much valued time.

—Seth

**6StringJazzer** · 11-23-2012, 10:22 PM

I'm trying to understand but not getting it very well. First off, your attached images give me the following error message:

Originally Posted by Excel Forum

Invalid Attachment specified. If you followed a valid link, please notify the administrator

Second, I think a fundamental part of your problem is that you are writing code for a workbook with worksheets that can be added and deleted, but you are hard-coding the names of those worksheets. I don't see why you have to rename all the sheets and moves all that data around just because you add a sheet. I suspect it is because in your code you refer to Sheet13, for example. And if you insert a sheet, Sheet13 goes from being the 13th sheet to the 14th sheet. That reference to a worksheet is call the code name, and it doesn't change. I think you need to use indexing, like Worksheets(2). For example

Please Login or Register  to view this content.

I'm not sure if this example does quite what you're trying to do here but it illustrates the concept of using indexes instead of the code name for the sheet.

Ultimately I need to understand you data layout and the relationship of data in one sheet to data in any other sheet. When you remove a person, you should not be hiding the sheet and jumping through hoops to move the data around. You should delete the sheet (if you don't need their data anymore!) and remove their data from the summary sheets.

**MAGICofSeth** · 11-23-2012, 11:15 PM

Hello 6StringJazzer,

Here is a link to a sample file from dropbox.

http://dl.dropbox.com/u/64034102/Tra...le%20File.xlsm

Hopefully that will help explain.

Unfortunately I need to have all 25 sheets available to be shown and named at all times because the general users of this spreadsheet would not be able to add the sheet and all the links that go to it. Each sheet has many formulas on it that are referred to by other sheets in the workbook. Because of this, actually deleting and adding sheets becomes an issue, and instead they need to remain in order and just be renamed.

I believe I am using the indexes you referenced, however please check out the sample workbook I posted and see if I am making any sense. The code is on the first sheet.

Thank you for your help!

**6StringJazzer** · 11-24-2012, 10:53 AM

I just cannot figure out what your code is doing without a lot more study.

I've modified your code in Module1; your code worked but did not use best practices.
One of your Subs does not compile
I've put comments in your code in GPS with minor changes. It will not be hard to find my comments because there were no comments to start with. This is another issue; I recommend using comments even if they just as reminders to yourself. You'll read this code in 6 months and won't remember what you were trying to do.
I also recommend you go to Tools, Options, and check Require variable declarations.

Your file is too big for me to post back but here is the code.

Module1

Please Login or Register  to view this content.

GPS, only the first two Subs. The comments and techniques shown here apply to the rest of your code in this module as well.

Please Login or Register  to view this content.

Also, in GPS this is preventing your code from compiling

Please Login or Register  to view this content.

VBA If is a little eccentric in that if you have

If <condition> Then <statement>

all on one line, there is no "End If". If you want the statement after the Then to be included in the code executed when the condition is true, simply move it to the next line.

**MAGICofSeth** · 11-26-2012, 03:28 PM

Thank you for your help cleaning up the code. The reason that specific sheets are called is because those are the sheets with the employee data and in turn, are the only ones that need to be hidden or shown.

Do you think it is possible to achieve what I originally posted about? What further information can I provide to help clarify the need?

I could make a video showing the problem if you want?

Any and all help is appreciated.

Thank you.

**6StringJazzer** · 11-26-2012, 10:56 PM

I recommend you rethink your coding approach to make the code not care about what order the worksheets are in. You need to tie the worksheets to the list of employees. Your original post said

I need some VBA Code (which this is above my skill level) that if a user enters the current name of the sheet to be removed, the VBA goes through the above process of renaming all the sheets after the one entered, copies the data from the sheet after to the sheet before for every sheet after the entered one, and then renames all the sheets to correctly correspond with their new data, and lastly for the new not needed sheet at the end name it whatever number from 25 it now actually is.

and I am trying to persuade you that this is a very complicated way to manage your data and indicates a design problem. I've shown the general approach for dealing with sheets by indexing them to the employee name, not sure how I can help other than rewriting all the code from scratch.