How to save a worksheet from VBA to a new book and path.

[sdiverdan]

I searched this forum looking for how to bring up the Excel SaveAs windows screen. I have a spreadsheet database that produces a worksheet resulting from the users search critera. I can bring up the print preview screen if they want to print the worksheet results. What I haven't been able to figure out is how to save the worksheet if they want to save their search. I want to save just that one worksheet and not the whole workbook. I would like them to specify the path (ie. where they want to save it) and the filename (ie. what they want to call it).
Is there a command to bring up the saveas window's screen? How does one save just one worksheet? I know about the GetSaveAsFileName and SaveCopyAs but do I have to create userforms to do what the Windows SaveAs can do?

Dan

 

[SkipVought]

Hi,

Check out the GetSaveAsFilename Method Method.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sdiverdan]

SkipVought, the GetSaveAsFilename saves the whole workbook doens't it. I don't want everyone to save a massive database/VBA spreadsheet just the small worksheet of their search with their own path and filename. The GetSaveAsFilename does give them the option of specifying a path and a new filename. I can even specify a text file versus a spreadsheet. I've not done this before so it's new to me. How do you specify only the activate worksheet versus the whole workbook to save. And on another point how can you specify that they can save as a non-macro spreadsheet or text or pdf?

If Excel/VBA fails at this I guess one option would be to create a new workbook and copy the worksheet into it and then do a GetSaveAsFilename.

Dan

 

[Gavona]

Record yourself moving the single sheet to a new workbook. (Right click on the sheet tab......). Then saving the workbook.

Examine the code and apply Skip's suggestion. Post back with your code if you have problems.

Gavin