Macro to save file based on cell value

nuffi

New Member
Joined
Jun 8, 2010
Messages
3
Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
This macro is amazingly straight-forward: <o:p></o:p>

Public Sub SaveAsA1()<o:p></o:p></PRE>
ThisFile = Range("A1").Value<o:p></o:p></PRE>
ActiveWorkbook.SaveAs Filename:=ThisFile<o:p></o:p></PRE>
End Sub<o:p></o:p></PRE>
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's<TABLE style="WIDTH: 61pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=81 border=0 x:str><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=81 height=40>T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..
</TD></TR></TBODY></TABLE>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Public Sub SaveAsA1()
ActiveWorkbook.SaveAs Filename:="T:\COMMISSIONING\IJT\TIMELOG project\Staff#1\" & Range("A1").Value
End Sub
 

nuffi

New Member
Joined
Jun 8, 2010
Messages
3
Hi. Thanks for the rapid response.
Unfortunately I still get the message "Excel cannot access the file". I wondered if it couldn't access the network and altered the script to a blank sheet I'd saved on my C drive desktop, but still got the same message. If I could just check I'm attempting to do it right?

  1. Cell A1 has a persons name in it.
  2. I've created a file for the person to click on an macro button to unsure it always saves to the correct existing file.
I knew it would be one of those days.......
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Can you navigate to that path manually - i.e. File > Save As.
 

nuffi

New Member
Joined
Jun 8, 2010
Messages
3
Success. Sorry to have wasted your time, I believe the problem was based on my using the hash key to identify the person. as soon as I used their name it saved perfectly.

Thank you very much.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

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