saving w/sheet to different location
 

Hi

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

Thanks

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=47357

saving w/sheet to different location
 

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" wrote in message
...

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

saving w/sheet to different location
 

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

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

saving w/sheet to different location
 

Hi

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

(There are 3 sheets on the workbook hense the name "esti3.xls" and it
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

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=47357

saving w/sheet to different location
 

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" wrote in message
...

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

saving w/sheet to different location
 

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

saving w/sheet to different location
 

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" wrote in message
...

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

saving w/sheet to different location
 

Hi Ron

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

Thanks again for your time

Regards

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=47357