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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
Oops! I should have spotted that. Try deleting that line of code - I don't think it is necessary.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,499
Messages
6,142,831
Members
450,449
Latest member
Dave Carr QM

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
Back
Top