Open New Workbook with VBA & Save Name & Location for Use in Other Macros

The easy way to pass data from one module to another is to define the variable holding the data as being global.

At the very start of your module, before any subs add:

Please Login or Register  to view this content.

Once your sub has set it you'll be able to read the value from other subs/functions.

This isn't great programming practice, but it works.

Better (slightly) would be to make your Sub a Function and pass the value back in a controlled fashion.

That works great, thanks!

So what about this part of the question?

Originally Posted by amyxkatexx

I'd like for it to save the Core Sheet at the end of all this with the name "Core_999999998_1949_mm_dd.xls" but give the user the option where to save it to. Is that possible?

Ah, the hard bit.

This little bit of code will tell you where the user has tried to save the file ...

Please Login or Register  to view this content.

You'll then need to truncate this at the last backslash and add your own filename.

Does that help?

Can I tell it to save it at some point to where the prompt comes up for them to save and the file name is already there, they just have to pick the location?

Yes, just set the Initialfilename...

Please Login or Register  to view this content.

This won't save the file, just return the selected path and save name. If you just want to go ahead and save it you can use:

Please Login or Register  to view this content.

If I use that second part though, won't it just save it in a location wherever they last saved something and possibly put it where they can't find it?

No, if you use the 2nd part it will open the file save as dialogue and let them select where they want to save the file, it will just auto-populate the save name.

You can add a default directory path as well, if you want, but be warned - trying to code around user stupidity is an endless, and mainly thankless, task.

Okay, so I seem to be unable to figure this one out...

Without choosing the filepath because these are Macs, so I can't do the whole C:Documents and Settings thing because the file path changes for each user and since Excel for Mac 2004 won't let you use relative filepaths, there's just no way to do this for multiple users.

So, without doing that, how can I...

1. Bring up the save as menu
2. With the name already populated as "Core_999999998_1949_mm_dd.xls" (mm & dd being the actual month and date)
3. And once they hit save, continue on with my macro (or even if they hit cancel)

I have a hard time piecing these codes together in the right order, so if you know how to and don't mind doing so, I'd really appreciate the whole code somehow implemented into my original post to where after it pastes the header row...

Please Login or Register  to view this content.

...it then goes through this save process.

I can't tell you enough how much help you've been so far and I really appreciate your time and patience with me!