I should have explained myself better.
I could have 1 or more worksheets I need to save. (there are other hidden worksheets I don't want included in the save as. I want to be able to select and save this group and ideally have the save as prompt pop up so I can give each save a new name. Is this possible?
A little tweak to what Billy provided you might do it:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
.SaveAs "C:\Documents and Settings\All Users\Desktop\" & _
InputBox("Please enter the Save As Name...", "Worksheet Save As") & ".xls"
I would like to add another twist to this problem.
Imagine I have a workbook with 10 sheets.
I would like to copy just 3 of those sheets (indicating which one I would like to be copied referring to the sheet name) into a new workbook and save that new workbook with a name given by the user and in a path set by me.