Save a copy of a workbook with a new name

[AndyTampa]

I have two workbooks that I open every day. Workbook #2 relies on cells in workbook #1 . Occasionally, I need to save Workbook #1 with a different name, but when I "save as", it changes the relationship source for cell references in workbook #2 .

What I need to be able to do is run a macro that will save the current workbook to a specific directory but ask me for a new name. It needs to do this without changing the name of Workbook #1 and breaking the cell references in Workbook #2 .

I've found stuff for ActiveWorkbook.SaveCopyAs, but it requires a file name in the code. I don't know how to create the dialog box to ask me which name and then insert it in the command with a specific path. I'd also like the dialog box to display this path.

It sounds simple, but I'm not familiar with VBA. I'm good enough to copy, paste, and occasionally edit.

Can someone help me please?

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

no? i don't see why you would have used your solution over my initial one on this post if you were not wanting the dialogue boxes?

The solution you referenced shows the path to be a static path. We've moved on to include a way to pick a location to save the file. However, this dialog box shows a field with the filename and gives the user the ability to pick a filename from the list of files in the dialog box either on purpose or accidentally. I'm not sure I like that. I'm trying to idiot-proof this as much as possible since I might give it to someone else to use. I didn't say I didn't want the dialog box. I said I wanted to eliminate two fields from the dialog box so that they would only choose a folder in which to save the file for which they've already chosen a name.

to my knowledge you can't restrict the basic functionality of a save as box, just the options it allows like selecting multiple files etc.
if you were wanting someone to select a folder to save in you could use file picker in the same kind of way we are using strResult

That answered one of my questions. We can't eliminate those fields. (Right?)

I haven't played with the code you just wrote yet. I will soon. But just know that I have been using the code you provide as well as others I've found to get a solution that works. Now I'm trying to improve upon it. With what I'm learning, or rather finding, I'm creating a better idea of what I want in the end.

As it stands now, what I'm aiming for is:
1. Determine the location of the original file to have a default path for the dialog box.
2. Determine the file type of the original file so the newly created file will be saved as the same type.
3. Ask for a filename using an InputBox that contains instruction text similar to what I've been using.
4. Ask for a folder in which to save the file. This dialog should only show folders and use the default path from #1 .
5. Copy all sheets to a new workbook.
6. Save the new workbook using the path, name, and extension from #4 , #3 , and #2 .
7. If there is no selection or Cancel is pressed in either of the dialog boxes, then close the new workbook and end the macro.
8. Once the new workbook is saved, it should close.

I'll post again once I've tried out the latest code you've posted. I'm having a little difficulty following it so far.

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

I didn't say I didn't want the dialog box. I said I wanted to eliminate two fields from the dialog box so that they would only choose a folder in which to save the file for which they've already chosen a name.

then the newest code i wrote should accomplish this

That answered one of my questions. We can't eliminate those fields. (Right?)

well not with save as, but filepicker has a different mechanic that doesn't involve the things you're talking about (thus i wrote it as the solution i my last post)

1. Determine the location of the original file to have a default path for the dialog box.

i currently have the set one to go to default (which is usually documents). To go to where the workbook is located replace the line with .InitialFileName =ThisWorkbook.Path & "/"

2. Determine the file type of the original file so the newly created file will be saved as the same type.

I'm unaware of how to do this but the internet is a vast place.

7. If there is no selection or Cancel is pressed in either of the dialog boxes, then close the new workbook and end the macro.

i would need to put in error handling for the folder picker, but i would like to know if its what you're looking for first beforehand