Comment Absent in the Copied Sheet to a New File created and Transferred using a Macro.

Dear Forum,

I have a Sheet which has a Comment, now I am replicating this sheet using a Macro and then the Sheet is renamed and then again using another Macro first a New File is created and then this New replica of this Sheet gets transfered to this new created file..

But in the new file , the comment does not show up..
Can someone please give me some inputs as what could be the reason for this..

Due to the sensitive nature of the data, I cannot upload the File but please find the code which is used to create the Replica and then transfer it to a new file..

Please Login or Register  to view this content.

Now there's some code above which is commented but actually earlier I was trying to create the Comment in the Replica Sheet and then just Move this Replica sheet with the comment to the new file , however that did not work so i just created the comment in the original though I would prefer to create it dynamically in the copied sheet and then just move it to the new file..

Please Login or Register  to view this content.

Thanks in advance..

Warm regards
e4excel

Hi, e4excel,

why not copy the sheet directly and work on it later on?

Please Login or Register  to view this content.

Ciao,
Holger

Hi, e4excel,

your code worked fine on a sample for me. Maybe you attach a copy of your workbook?

Ciao,
Holger

Hi, e4excel,

there are not very much things I know about VBA but if your Sheet "Index_Sheet" has a comment in Range("E5") and you copy this sheet (may it be within the workbook or in between workbooks) that comment will be there as well (this goes for any version of Excel 97 to 2010 for Windows).

Please Login or Register  to view this content.

This will delete comments as well if used in the code.

Ciao,
Holger

Hi, e4excel,

I´ll try to rebuild a scenario reflecting your situation. I just want to assure to do the right things and have to ask two things: you indicate to use Excel2007. Is that true? And if so what is the standard fileformat for saving new files on the system?

Might be that I´m missing something obvious but I still can´t find it though (maybe too early in the morning by now).

Ciao,
Holger

Yes I am using 2007 and want the Comments to show up in the Copied and Transferred Sheet..
I am sorry for replying late but actually net speed was pathetic so I could post my replies in the last two days...

Hi, e4excel,

result to my test was like I expected it to be: the newly created file with the code supplied (and some small alterations) shows the comment.

Altered code for MoveIndex:

Please Login or Register  to view this content.

Tested with Excek2007 on WinServer2003, Fileformat is xlsm on the system. Both workbooks are in 97-2003 Fileformat.

Ciao,
Holger

Hi, e4excel,

sorry but I took your codes (only commented Call DeleteSheets as this was not supplied) and wrote what combination I used. I saved to a location which is considered in my Trust Center. Sorry but I´m out of ideas how to solve your problem for the moment.

Ciao,
Holger