Macro "save as" breaks hyperlinks

[peggyprentice]

I have a daily report that needs to be saved after selecting all cells, copying, and pasting special values to get rid of the formulas. It also has a series of hyperlinks that jump to specific worksheets in another Excel file. When I manually "Save as" another filename, everything is OK. When I put the "Save as" in a macro, the hyperlinks are broken. Also, conditional formatting is lost.

How can I fix this? I'm not a VBA expert - the macro was built using keystrokes.

Thanks in advance for any help.

[Sisyphus]

Hi,
Maybe you can let us have a look at the macro?
Make a copy of your workbook. Delete all data from it except for one hyperlink (and its destination, if it is within the same workbook) and, perhaps, one conditionally formatted cell. If you leave a conditional format you also must leave the referenced cells intact. Post that workbook. The code will be attached to it. But I suggest that you try once to save it using the macro, just to make sure that the abstracted version behaves the way you expect.

Have a great day!

[Don]

It sounds like you might be copy-pasting-special-values over everything and perhaps that is causing your problems.

[peggyprentice]

Yes, I am copy-pasting-special-values over everything, but when I do it manually the hyperlinks are all intact. It's only when I add the "save as" command in the macro that they get lost. If I save as manually, they stay intact.

I will put out a copy of the macro code but it will probably be Monday.

Thanks in advance for the help.

[Sisyphus]

Hi peggyprentice,
Looking at the problem is a pragmatic step toward solving it. But Sherlock Holmes taught us that logical deduction also leads to knowledge. So, here goes:

A hyperlink to another workbook contains a path, say, "C:\My Documents\This subject\myfile.xls". Now, if you were to save a file from where this address isn't accessible the link would be "lost". Pragmaticism requires that we look at your code but in the light of the above (Holmes) I doubt that we will find the solution there. You may be quicker by examining how the change of file name and location affects the missing links.

In other words, it isn't, most probably, the Paste Special that is causing the problem but the SaveAs. And it shouldn't be the SaveAs command that is causing the problem but the relative change of names and locations vis-a-vis the hyperlinked files. We may not find this in the code.

As for the conditional formatting, King Solomon might say that it is either related or it is not. Again, we may not be able to tell by what you post. But you should be able to tell pretty quick if the conditions are somehow affected by the hyperlinked data that suddenly turn up missing. My suggestion is to ignore this subject until the hyperlinks problem has been solved. If, then, it turns out not to be related, meaning it didn't go away by itself, post the problem in a separate thread.

Have a great weekend!