Save As file name-Macro

dlove

New Member
Joined
Mar 20, 2009
Messages
34
Ok,

I have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.

What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.

Any assistance?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

dlove

New Member
Joined
Mar 20, 2009
Messages
34
Here it is.

Thanks!



Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\drew\Desktop\Customer Quote Testing.xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\Customer Quote Testing.xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: change the sheet name to suit

Rich (BB code):
Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\drew\Desktop\" & Sheets("Sheet1").Range("D10").Value & ".xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\" & Sheets("Sheet1").Range("D10").Value & ".xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub
 

dlove

New Member
Joined
Mar 20, 2009
Messages
34

ADVERTISEMENT

I appreciate the help with the code. If I send this code to somone else, will the macro recognize to try and save the file to their desktop and not mine? If not, then how would we write the code to save the copy to whatever desktop the macro is being run at?

Thanks,
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You could try this

Code:
Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & Environ("username") & "\Desktop\" & Sheets("Sheet1").Range("D10").Value & ".xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\" & Sheets("Sheet1").Range("D10").Value & ".xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub
 

dlove

New Member
Joined
Mar 20, 2009
Messages
34

ADVERTISEMENT

It seems to be closer. The first line of the code is still giving the recipient problems.

ChDir "C:\Documents and Settings\drew\Desktop"

The above line of code gets an error. I'm guessing it is because my username is specified on that line and it gets confused when it runs the code from a different desktop. I appreciate your assistance on this issue! I will continue to try different things. Further help is appreciated.

Thanks,
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Oops! I should have spotted that. Try deleting that line of code - I don't think it is necessary.
 

Wayne01

New Member
Joined
May 27, 2009
Messages
38
Hi Guys

I have used the macro to export a chart to an image file but have noticed that my larger charts seem to loose some of the chart image to the right hand side of the image. Does anyone know how to solve this issue? can we specify an image height and width?

Regards
Wayne
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top