saving w/sheet to different location

[Nigel]

Hi

I am a complete novice with vba and have a worksheet which I would like to save to a folder by pressing a command button. The worksheet needs to be saved with the variable contents of cell C2 as the name. I tried recording a simple macro which almost worked but I couldnt get it to "save as" using the variable contents of C2 as the name. Can anybody help me with this please?

Thanks

Nigel

[Ron de Bruin]

Hi

Try this
Look also in the VBA help for SaveCopyAs

Sub test()
With ThisWorkbook
.SaveAs "C:\" & .Sheets("Sheet1").Range("C2") & ".xls"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nigel" <[email protected]> wrote in message
news:[email protected]...
>
> Hi
>
> I am a complete novice with vba and have a worksheet which I would like
> to save to a folder by pressing a command button. The worksheet needs to
> be saved with the variable contents of cell C2 as the name. I tried
> recording a simple macro which almost worked but I couldnt get it to
> "save as" using the variable contents of C2 as the name. Can anybody
> help me with this please?
>
> Thanks
>
> Nigel
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile: http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=473579
>

[Nigel]

Many many thanks Ron de Bruin for taking the time to reply...........will give that a go.

Regards

Nigel

[Nigel]

Hi

Couldnt seem to get that to work......then looked up the "saveas" function on this site and the formula suggested looked far too complicated for me!......I have Recorded a macro which I have assigned to a command button and it works, except I need to modify it to "saveas" the contents of cell C2 as the filename. Could you help me modify this formula please?

(There are 3 sheets on the workbook hense the name "esti3.xls" and its only the active sheet I want save when the command button is pressed).

Private Sub CommandButton4_Click()

ActiveWorkbook.SaveAs Filename:="G:\DOCS\ESTTRI\esti3.XLS", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

End Sub

Any help would be greatly appreciated.

[Ron de Bruin]

Ahhhaaa

Try this then

Private Sub CommandButton4_Click()
Call test
End Sub

Copy the macro in a normal module

Sub test()
Dim wb As Workbook
ActiveSheet.Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:="G:\DOCS\ESTTRI\esti3.XLS"
wb.Close False ' close the file with the activesheet
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nigel" <[email protected]> wrote in message
news:[email protected]...
>
> Hi
>
> Couldnt seem to get that to work......then looked up the "saveas"
> function on this site and the formula suggested looked far too
> complicated for me!......I have Recorded a macro which I have assigned
> to a command button and it works, except I need to modify it to
> "saveas" the contents of cell C2 as the filename. Could you help me
> modify this formula please?
>
> (There are 3 sheets on the workbook hense the name "esti3.xls" and its
> only the active sheet I want save when the command button is pressed).
>
> Private Sub CommandButton4_Click()
>
> ActiveWorkbook.SaveAs Filename:="G:\DOCS\ESTTRI\esti3.XLS",
> FileFormat:= _
> xlNormal, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:=False _
> , CreateBackup:=False
>
> End Sub
>
> Any help would be greatly appreciated.
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile: http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=473579
>

[Nigel]

Hi Ron

Thanks for replying.........I cant test the vba program you kindly wrote for me untill I go back to work tomorrow.....however looking at it (and what do I know!)....I cant see any reference to cell C2.....As the sheet is like a template and will be used over and over again I need it to be saved to a seperate file with the variable contents of C2 as the file name...........Just for the record I have another command button on the same sheet which when pressed clears the "template" of all added information and returns it to the basic form again ready for new data entry..........(somebody else on here told me how to do that!)......I hope you can get some idea of what Im trying to achieve from the above description and do you think your prgram suggestion will work ok?

Regards

Nigel

[Ron de Bruin]

I copy your code but you can look in my other reply how you must do that now

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nigel" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron
>
> Thanks for replying.........I cant test the vba program you kindly
> wrote for me untill I go back to work tomorrow.....however looking at
> it (and what do I know!)....I cant see any reference to cell C2.....As
> the sheet is like a template and will be used over and over again I
> need it to be saved to a seperate file with the variable contents of C2
> as the file name...........Just for the record I have another command
> button on the same sheet which when pressed clears the "template" of
> all added information and returns it to the basic form again ready for
> new data entry..........(somebody else on here told me how to do
> that!)......I hope you can get some idea of what Im trying to achieve
> from the above description and do you think your prgram suggestion will
> work ok?
>
> Regards
>
> Nigel
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile: http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=473579
>

[Nigel]

Hi Ron

Many thanks, with your help I have been able to work it out.....(learnt a lot too!)

Thanks again for your time

Regards

Nigel