Save copy of active sheet - values and formats only

[Lance Fairway]

I use the code below to save a copy of my Workbook in a specific file
path (testing to see if the folders already exist along the way) with a
specific, data dependent file name. It works, but I'd like to tweak it
a bit and I'm not sure how to write the code.

I'd like to save only the active sheet instead of the whole workbook.
I'd also like to save only the values and formats to the new file, not
the formula and macros.

Anyone have any advice on how to accomplish this?

Here's my current code:

Sub SaveName()
If Not Len(Dir("g:\users\one\" & Range("h6"), vbDirectory)) <> 0 Then
MkDir "h:\users\one\" & Range("h6")
End If
If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3"),
vbDirectory)) <> 0 Then
MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3")
End If
If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3") & "\"
& Range("g7"), vbDirectory)) <> 0 Then
MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3") & "\" &
Range("g7") & "\"
End If
ActiveWorkbook.SaveAs Filename:="h:\users\one\" & Range("h6") & "\" &
Range("e3") & "\" & Range("g7") & "\" & Range("e3") & "ControlSheet" &
"." & Left(Range("b7"), 31) & ".xls"
End Sub

 

[Stopher]

You might have to do a cut and paste special into a new workbook with
the sheets you want. Also with your code it seems that it is only
looking at the active sheet in the active workbook as there are no
sheet qualifiers in your code ie sheet1.Range("h6").
Is this always the case or is it just grabbing a set of cells on some
active sheet to create the concatenated file name, which you probably
store on the sheet some where instead of the complex range + range you
have?

Just a few question I need to know really.