Create and Save Workbook to Current Directory Using VBA

[amanda12345]

Hi,

I am new to VBA. I am trying to create a code that will copy a range from one worksheet (Unit Mix), paste it into a new workbook as values only, then save that work book under the name Unit Mix to the same directory as the original workbook. Currently, it appears to be working except for the fact that is saves the new workbook to my documents, rather than the current directory of the original workbook. Here is what I have so far:

Please Login or Register  to view this content.

[Solus Rankin]

Try changing the end of your string variable to & "\Unit Mix.xlsm"

Also it won't have a path if the first workbook hasn't been saved yet.

[amanda12345]

I believe this is what you suggested--

Please Login or Register  to view this content.

However, when I try this I receive the following error message:

Run-time error '1004':
This file extension can not be used with the selected file type.

If I leave off the .xlsm, it saves the folder to my C drive.

The first workbook is saved to a specific directory, but this file will be used as a template and will be moved around to different directories, so I would like to find a way to make sure that the new workbook I am creating will follow this original workbook whereever it is moved.

Thank you!!

[Solus Rankin]

On a side note. Please use code tags. To use code tags highlight your code and press the # button in the tool bar

Change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Edit:

I just realized you don't want to use activeWorkbook because you created a new one. You should qualify which workbook for which you'd like to use the path.

[arlu1201]

Amanda,

Welcome to the forum.

I have added code tags to both your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[amanda12345]

Thank you so much! That worked, I just have one small issue. The name of the original workbook will change frequently since I will be using it as a template and renaming each time. Is there anyway to rewrite it so that it doesnt not specifically reference the original workbook name?

[Solus Rankin]

Try:

Please Login or Register  to view this content.

At the top, then your string becomes:

Please Login or Register  to view this content.

[amanda12345]

I wasn't clear on exactly where to insert the first part of the code, but tried it at the very top, as well as like this:

Please Login or Register  to view this content.

Both ways give me this error:

Run-time error '13':
Type mismatch

[Solus Rankin]

Try it here:

Please Login or Register  to view this content.

Note the changes I made in relativePath =

[amanda12345]

Thank you!

[Solus Rankin]

No problem glad to help. If you are satisfied with the solution please mark the thread as solved.

And if you don't mind adding reputation by clicking the * below my post.