excel to excel, excel to word

[nancyo]

I am looking for a simple code to do the following:

1. Copy ONE spreadsheet from an excel workbook into a new workbook.

2. Create the new workbook as part of the macro.

3. Save the new workbook as part of the macro.

4. The saved filepath will be different only in the FILENAME, which somehow must be referenced from the original excel file (possibly from a specific cell reference from a different spreadsheet).

5. Fantasy macro - do the same to word.

I have tried several variations on macros offerend in this website and others, and can't get anything to work. I am not a programmer at all, but have been able to modify other macros in the past to work.

Any help is appreciated!!!!!!!

 

[NateO]

Not sure what 9 is, but recheck your spelling and I believe the sheet name in quotes is Case Sensitive.

If this doesn't solve the issue, show the entire code and where the breakdown occurs.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 06:25

 

[nancyo]

OK - I got the my macro to finally work...
Now when i go to open the new workbook, the "links" warning comes up. So, I went into edit, links, change source, etc. and tried to reference the new file, and it won't let me. I really don't want this link warning to remain, is there any way I can disable another way?

The reason it is linking in because my filename is a reference from the original workbook...
This message was edited by nancyo on 2002-05-01 08:45

 

[NateO]

It's not the filename (double-certain), it's a formula or a named range. Recheck your formulas in your new workbook. Press ctrl & ~ at the same time and browse for formulae with links. You may want to consider pasting these as links or finding a different way to construct your worksheet. And look at the named ranges in the new workbook. Hope this helps.

Incidentally, whassup with the new MrExcel pop-up advertisements, wowee zowie.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 08:50

 

[nancyo]

NateO: You are right, the original spreadsheet contains formulas linking to other spreadsheets in the original workbook. My problem is the people who will be using this new workbook - I have to basically have it idiot proof.

I need to rethink my macro - maybe I can add a section in the macro to pastespecial "values"???
This message was edited by nancyo on 2002-05-01 08:54

 

[NateO]

Yep

pasting these as links

should've said links as values, fingers are moving faster than the eye. That's the way (uh-huh, I like it...), find the cells in question and use code like this:

[a1] = [a1].value 'pastes cell a1 as value

You can do this after the copy and before the save. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 09:16

 

[nancyo]

NateO - I REALLY appreciate all your help - I have no one at our company to discuss this stuff with, and pretty much all the stuff I do is trial and error...which is very frustrating.

Anyway, my macro is working OK without adding your line of code. It appears that if I save some of the formulas "as values", then the links appear. If I leave all the formulas as formulas (some of which actually return text and not values), then I do not get the warning. As I said, trial and error. This may have to be good enough for now...I think I will move on to trying to get this to word.

THANKS AGAIN!!!!!!!!!!

 

[NateO]

Text and numeric values are coincidence, that's not what [a1].value is referring to. That line simply means the solution to the formula. You'll still have links irrespective as to whether the formula result is text or numeric. Food for thought. Gald to be of help, good hunting.

 

[nancyo]

So what is the difference: why would sometimes a warning appear about links when the file is opened, and sometimes not?
If my formulas link the two workbooks, then the new workbook will automatically update if there are changes to the original spreadsheet. Therefore, theoretically, I should not be getting any warnings at all..

Soory to ask such stupid questions...

 

[NateO]

I think the difference is as follows. If you have the linked workbook and the master workbook open at the same time (i.e, the master is open and you open the new file) the new file will update automatically, no prompt. If the master is closed, you will be prompted to update the links. This should be irrespective of text or numeric formulae results.

Your end-user, who does not have access to the master file should wind up being prompted every time.

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 10:28

 

[nancyo]

OK - The macro is all set. Does everything that I want, finally. I added some steps in the middle of the macro to copy/paste special all the cells with formulas. Now, if the master file is closed, there are no more links. THIS IS HUGE FOR ME!!!!!! THANKS
NATEO AND ALL WHO HELPED!!!!