naming a workbook from a cell (macro?)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A user wants to name a workbook the name that is entered in a particular cell. (A Purchase Order, for example). After they populate the cell, can a macro be executed to copy the cell, and paste into the save as dialog box? I've tried, but the paste doesn't work for me... We are using Excel 2003.

Thanks
 
Eric

You can paste this code into the worksheet module concerned. Right-click
the sheet tab and select 'view code'

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
ThisWorkbook.SaveAs Filename:="C:\" & Target.Value,
FileFormat:=xlWorkbookNormal
End If
End Sub

This will test if the entry is in A1 on that sheet and if it is will save
the workbook, in the root directory of the C drive as the entry in A1. (Be
aware there is no error checking, so if you enter a date as 01/01/2004 for
example it will error as you cannot have slashes in a filename).

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Eric Jeppesen said:
A user wants to name a workbook the name that is entered in a particular
Click to expand...
cell. (A Purchase Order, for example). After they populate the cell, can a
macro be executed to copy the cell, and paste into the save as dialog box?
I've tried, but the paste doesn't work for me... We are using Excel 2003.
 
Back
Top