I have a macro that takes a sheet and splits it into several different sheets, using various loops.
I am trying to rename the sheets using this code
It just runs through and doesn't change the name of the sheet.
Please Login or Register to view this content.
Try this...Please Login or Register to view this content.
If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
I tried this on the worksheet both on it's on and by adding it to my code and it does nothing.
Now the values being used come from cells that I used Text to Columns on. I have no idea if that makes any difference or not. But I have used this code in other macros and it was fine.
Here is the full code of the macro, maybe this will help
Please Login or Register to view this content.
It would probably be a good idea to upload the workbook, or a sample, if the data is not sensitive (or can be anonymised)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I have uploaded a Dummy worksheet
DUMMY2.xls
I wil have to make some edits to that worksheet
This one should work a bit better as I ranamed some cells for it to work.
DUMMY2.xls
Cell C8 does not exist in its own right. It's merged with cell B8 ... so it's not going to have a value and all the conditions fail.
Regards, TMS
Two things I noticed:
1. If you put my procedure above the Next ws statement, it will cause my procedure to run through every sheet exponentially (ie if you have two sheets it will execute your code twice and my code twice for four executions). Place my code minus the sub end sub parts right below the Next ws statement.
2. Change cell G2 on your original sheet to one of your test values and unmerge the cell, then run the macro. The original sheet will be renamed O- if you enter Opportunity in G2...
Sorry, where is your original code in relation to this workbook?
And G2 and H2 are part of a merged cell, A2.
I'm kind of confused here ...
OK... What I did was take A1 and used the Text to Columns to seperate the data in A1 so I can use it. It may have had something like, Issue - I-10 - Problem name and details or Risk - 2 - Risk name and details
This database wasn't exactly normalized so it gets tricky.
I seperated A1 by the deliminater -
Then I did some checks just to get Issue in one cell and the number in the next cell
I created a G2 and H2 just to check things out but G1 and H1 could be used as well.
A2 is not merged
You need to run the macro that I have created and posted... I made a copy of the orginal sheet and unmerged the cells and got rid of extra columns.
DUMMY3.xls
Sorry, I posted the DUMMY2 worksheet before I left work on Friday.
I have reposted DUMMY3 and it has the actual code that is to be used. Maybe this will make more sense.
The Macro unmerges the cells so there are no merged cells being used.
I would approach the creation of the Copy sheet and then copying the data to it something like this:
Please Login or Register to view this content.
That would create and name the Copy sheet and, without needing to select the RiskListWithResponses sheet, copy the data to it.
If all the borders are the same, the adding borders code could be reduced to:
Please Login or Register to view this content.
Regards, TMS
Try the amended code below. The problem with the macro is that it was testing for the exact text "Risk", "Opportunity" or "Issue" when the text in each of those tests has a space at the end of it. To remove this problem, I used the Trim function to remove the space. Note that this still leaves some worksheets that don't get renamed since G2 contains text like zzzzzzzz. The amended code looks like this:Please Login or Register to view this content.
Thank you tlafferty, that solved my problem.
Glad I could help! Please mark the thread solved (forum rule #9):
http://www.excelforum.com/forum-rule...rum-rules.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks