Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems saving a worksheet with links to a New Location | Excel Worksheet Functions | |||
Saving Excel in more than one location | Excel Programming | |||
VBA for saving a file to a specific location | Excel Programming | |||
Saving in a location | Excel Programming | |||
Programmatically saving a workbook to a ftp location fails | Excel Programming |