Copy entire sheet, and name it according to a cell

[gnoke]

Hi,

I have one sheet named "week". I would appreciate a macro that copies it into a new sheet and name the new sheet according to the text in cell A10 and A11 (combined cell).

Can someone please assist me?

Gnoke

[royUK]

Take a look at the Summary Sheet here, it contains code to do what you want

[gnoke]

Hi and thanks for your replay!

I`ve looked at the summarysheet, but I can`t make it work with my code.
I use this very simple code:

Please Login or Register  to view this content.

I would really like the copied sheet to be named according to the value in cell A11:A12, which is "January".

Can you please write out my code?

Gnoke

[gnoke]

Anyone else got an idea?

[royUK]

Presumably, A11:A12 is a merged cells. Try

Please Login or Register  to view this content.

[gnoke]

Hi and thanks Roy

You are right, they are merged cells.
Thanks to you I was able to write out the following formula that nearly deos the trick:

Please Login or Register  to view this content.

The problem is only that it takes the name of the values in cell A10:A11 of the main sheet.
I would like to name the sheet after the cellvalues in the "Copy sheet"

Any suggestions?

Gnoke

[royUK]

Are you changeing the cell value to the new name before copying?

I've just realised that your cells are merged vertically so the cosde should be

Please Login or Register  to view this content.

[gnoke]

Runs smoothly
One last issue however.
Rows 14 - 23 are merged vertically and horizontaly from A - O as well and holds text. In the copied sheet, only the text from rows 14-15 are shown, the rest are blank.

Any ideas why it is so?

[royUK]

possibly the cells contain more than 256 characters

[gnoke]

Again you are quite right.
Is the a simple way to get around this issue, or do I have to live with it:P?

Anyway, thanks a lot!

[gnoke]

Hi all,

I am using the last code provided by royUK above.
As written cells A11:O23 are merged an filled with text. However more then 256 characters are present.
Anyone whi knows how to modify the code to get around this problem?

Gnoke

Duplicate:
http://www.mrexcel.com/forum/showthread.php?t=486190