naming multiple tabs sequentially

We have these worksheets that have 100 tabs each
each tab is named joel_1400, joel_1401...Joel_1499 insert data in each tab template as needed for RFI's. then we have to make another worksheet with 100 tabs for 1500 to 1599

what we are doing is copying the whole worksheet and then erasing all of the user fields and changing all of the names manually for each tab

Any ideas?

Hi

The code below will rename the sheets. However, you will have to give details of which cells to clear on each sheet to allow us to give clearing code.

Please Login or Register  to view this content.

HTH

rylo

wow that was unbelievable (the code for renaming mutiple tabs sequentially). Being a microsoft newby, I didn't know what to do with the code, but I eventually pasted it into a Visual Basic macro or subroutine and it worked. Super. Now if I can, at the same time:

Change all the tab colors back to default
Change field I8 to the same number I guess it's "i"
Clear out D22, D23, D24 and A28 some of which may be merged fields but say that at the top when clicked on

Thanks a lot

Hi

OK, try this.

Please Login or Register  to view this content.

rylo

I love this place. I can't wait to get back to work and try it out (I never thought I'd hear myself say that, hehe)

the sub worked really well, thank you. In fact it's good enough right now, but being greedy, I was wondering how to handle the merged cell condition because I had to delete this line from the script:

.Range("A28,D22:D24").ClearContents <-- error

because I got a "can't clear contents of a merged cell" something like that

Thanks a lot

Hi

What are the merged cells that intersect with the nominated cells?

rylo

A28, B28 and C28 are merged


thanks

Hi

Try changing it to

Please Login or Register  to view this content.

and see if that works.


rylo

So far everything is really good. I used the last program you wrote to delete the stuff in merged fields that were on a single row. The last problem I'm having is that one merged cell is on 2 rows and several columns, namely from
A28 to U29 and I get the error whenever the merged cell is multi-row. Thanks
(PS. I may have been wrong last time when I used some cell references since I was at home and not looking at the file, but I was able to adapt the information to suit. thank you)

Hi

Try putting in the full merged range and see how that goes. (A28:U29).

rylo

That didn't work, but we're almost there. I appreciate your help; what we have so far is totally great and saves a ton of time. If we can't easily solve the clearing of the merged cell that includes multi rows, then if we could manually make the first tab what we want, then automatically copy 99 more tabs exactly the same, then run the routine we already have that will number sequentially, I'd be ecstatic

is there a way to copy 99 more tabs exactly like the first tab (supposing 100already exist, or supposing that there is only 1 tab in the worksheet, either way)

Hi

Can be done, but it would probably be easier to (a) select the merged cells, (b) unmerge them, (c) clear the data from the cell that has the entry and (d) then remerge the cells.

Can you advise (a) which cells are merged that you want to action, and (b) which cell in the block contains the data to clear.

rylo

well I'm not at work now, but I'm pretty sure the merged field is the following cells

A28:U29

and the data is in
B28

even if it wasn't, I'm sure I could make the adjustment

thanks a lot

I am at work now so I have access to the workbook sheets. I hope you had a nice weekend. Thank you for your help so far. The merged cell is from A28 to U29 and the data is in A28. thanks

H

Try

Please Login or Register  to view this content.

rylo

suuuweeeeeet
I can't wait to get to work and try it. There I go again with the 4-letter words (work) lol,
Thanks

Everything worked

my boss thinks I'm a genius

Thanks a lot

Did 15 hours work in 10 seconds hehe