Macro to Create a New File and Save as Option

Hi everyone,
I would like to ask for help on some macro scripting.
On my dummy sheet I have some data and a save button.
What i would like to do is when i clicked the save button,
Only the data (A5 to F20) will be copied on a different excel file and I will be prompted to save it as
A new Excel file. Can this be done?

Hope the scripting gurus will be able to help me again. Thank you in advance guys

This can be done but you would have to specify if the range is always going to be the same? Otherwise you could copy the entire sheet and paste it in a new workbook.

I'm going to assume it will alway be A5:F20

Hi,

I've attached a way of doing this provided the range is always going to be the same. When you click the command button it will ask for the file name and then save it to the folder path of the current file. This is the code I used:

Please Login or Register  to view this content.

I created a named range in the source workbook called CopyRng and as it was only values used an array to copy it to the new workbook.

Hope that helps.

Hi,

If you want formatting as well you would have to change the code I used. If you press ALT+F8 it brings up the macro run dialog. There should only be one called SaveAS, if click on it so it is highlighted then from the buttons on the right select Edit. This will take you to the VBE and to the correct module. All you have to do now is change all the code with this code below:

Please Login or Register  to view this content.

This physically copies the range to the new workbook so any formatting or formulas will also be copied.