Trying to save a worksheet as a new workbook

[subbby]

• I have a sheet full of data.
• All this long I had written the code to save the selected cells as a PDF.
• But now I want it to be saved as a separate file. In this case, I believe, first the VBA will have to open a new workbook. Then copy paste this sheet onto one of the sheets in the workbook . then save the .XLM file.

The code I have now is :

Please Login or Register  to view this content.

What I tired to do :

Please Login or Register  to view this content.

But didn't work... Any idea how to resolve this?

[Norie]

You can create a new workbook from a single sheet like this.

Please Login or Register  to view this content.

You can the save the new workbook as an xlsm like this.

Please Login or Register  to view this content.

[subbby]

Appreciate your help. It worked...

Thanks.

[subbby]

You can create a new workbook from a single sheet like this.

Please Login or Register  to view this content.

You can the save the new workbook as an xlsm like this.

Please Login or Register  to view this content.

It worked for a while.. then all of a sudden give this error

"this extension cannot be used with the selected file type. Change the file extension in teh File Name text box or select a different file type by changing teh SAVE as type..."

So here is how my code looks now

Please Login or Register  to view this content.

Since my code asks the user to input the file name ("fName") , is there a way it by default selects Save as type as "Excel Macro-Enabled Workbook" ???

I thought that the line in ur code should do it ... but it isnt. I have to manually change it to "Excel Macro-Enabled Workbook"

[Norie]

What type of files are you dealing with?

What's you default save as file type?

[subbby]

Default it depends from user to user. This program is used by many engineers. Some may have PDF as default, some as XLS and some as XLM

[Norie]

So the user could be opening PDF files?

[subbby]

So the user could be opening PDF files?

Norie , I quite didnt understand ....

My need is to save the file as a XLSM. The above code does it. However I ahve to double check the "SAVE AS" option in the windows dialog box if its XLSM. If by default its PDF, the above code gives error