excel file-save automatically (following a particular filename series) 1

[adhhealth]

Hi,
afraid if it is a silly question.
One excel file has 5 sheets.(say filename f1)
sheet1 is the master, the 5 sheets will have similar content as sheet1 but in a different format.
sheet 1 has 5 buttons
create s2, create s3 and so on..
when the user clicks on say create s2, sheet 2 should be automatically save as a separate excel file in E:\FOLDER1\S2\f1s2.xls.

to do this, i have created a macro and assigned it to the buttons, but cannot figure out how to automate the file name as "f1s2.xls"
pl. advise.

 

[SkipVought]

Hi,

Turn on your macro recorder and SaveAs.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

if i turn on the macro recorder and save as-i am entering the file name by my self,( i want it automated)
let me explain this more clearly,

i have multiple master excel files (say f1 f2 f3 f4 etc..)
when i click on create s2 button from master f1 it should automatically save as f1s2.( donot want to enter the filename manually)

creates 2 from master file f2 it should automatically save as f2s2 and so on..

 

[SkipVought]

please post your code.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/4/2005 by adh
'

'
Sheets("sheet2").Select
Sheets("vrfq").Copy
ChDir "E:\f1"
ActiveWorkbook.SaveAs Filename:="E:\f1\f1s2.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

this is the code. but here i am typing f1s2 manually..what i need is that it should pickup the filename of the master file (f1)from which i am clicking the button sheet2 is the sheet i am copying.
thanks for the response.
regards,

 

[adhhealth]

sorry for the mixup
the 2nd line of code is sheets("sheet2").Copy

and the file name is "E:\f1\f1sheet2.xls"

 

[SkipVought]

Sub Macro1()
    sFile = ActiveWorkbook.Name
    Sheets("sheet2").Select
    Sheets("vrfq").Copy

    ActiveWorkbook.SaveAs Filename = sFile, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

thanks for the response ..my requirement is i have to concatenate sFile with the string "sheet2" (so that the file name is f1sheet2.xls

i tried sFile+"sheet2"

but it saves the file as f1.xlssheet2.

 

[SkipVought]

a = split(sFile, ".")
sFile = a(0) & ActiveSheet.Name & ".xls"

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Thank you!!