save button in excel to save one of the worksheets with a cell value as its name

[Colin]

Hi
Just wondered if someone could help me with what I'm sure
will be an easy solution, but obviously hard if you don't
know how.

I have a spreadsheet with two worksheets, what I want to
do is have a button on the first sheet that will save the
second sheet as a seprate .xls or better still an html
page using the value of a specific cell as the name of
the file.

i.e I have two worksheets, named sheet1 and sheet2 for
example, if the value in cell A6 is 20040904 then
clicking on a button on sheet1 would save sheet2 as
20040904.html or 20040904.xls

is it possible and if so can someone please let me know
how

many thanks, Colin

 

[Tom Ogilvy]

Dim sStr as String
sStr = worksheets("sheet1").Range("A6").Value
worksheets("Sheet2").Copy
ActiveWorkbook.SaveAs "C:\Myfolder\" & sStr & .htm, FileFormat:=xlhtml
ActiveWorkbook.close Savechanges:=False

 

[Colin Evans]

Hi Tom

many thanks for the reply, I'll give it a go, once again many thanks for
taking the time to respond and offering your expertise.

Colin