Excel ‘Save As’ cell contents (Excel 2000)

Topic

New Reply

I have a spreadsheet that is “Read Only” protected and used as a template for other
spreadsheets. The other (new) spreadsheet will contain a cell that is unique to the
new spreadsheet. I’d like to create a button (from the ‘Control Box’ Toolbar ) in the
template spreadsheet that will create an unprotected new spreadsheet and save
it using the contents of the unique cell as the file name, then return to the new
spreadsheet for editing.
Is this possible ?

Reply | Quote

Replies

I’m confused by all these new workbooks:[indent]

---

I have a spreadsheet (A) that is “Read Only” protected and used as a template for other
spreadsheets. The other (new) spreadsheet (B) will contain a cell that is unique to the
new spreadsheet. I’d like to create a button (from the ‘Control Box’ Toolbar ) in the
template spreadsheet that will create an unprotected new spreadsheet (C) and save
it using the contents of the unique cell as the file name, then return to the new
spreadsheet (D) for editing.

---

[/indent]Is C to be created using A as a template, or is it just a blank new workbook?
Is D = B, or D = C?

Reply | Quote

Funny how in one persons mind it can be clear, but when questioned it is murky.

Spreadsheet (A) is “Read Only” and used as a template for other
spreadsheets (. The ( spreadsheet will contain a cell that is unique to the
( spreadsheet. I’d like to create a button (from the ‘Control Box’ Toolbar ) in the
(A) spreadsheet that will create the ( unprotected spreadsheet and save
it using the contents of the unique cell as the file name and stay in the (
spreadsheet for editing.
I hope this helps.

Reply | Quote

It’s still not clear to me. If ( is to be created from (A), where does the unique value of the cell on ( come from? If you create a new workbook based on (A), it is an identical copy of (A), so how can a value be unique to (?

Reply | Quote

A button (from the ToolBox) in (A) will create a value in (A), save that (A) spreadsheet then,
create ( and populate a cell in ( with that value. I’m currently working on the macro to
put the newly created value in the next empty cell in a specific column.
A couple of years ago I did something very similar to this in QuatroPro. Since then we
have migrated to MS Office and I’m trying to remove the human-error risk as much as possible.

Reply | Quote

If the unique cell is Sheet1!A1, this simple code would seem to do what you ask:

Private Sub cmdNew_Click()
Dim strFilename As String
strFilename = Worksheets(“Sheet1”).Range(“A1”) & “.xls”
ThisWorkbook.SaveCopyAs Filename:=strFilename
Workbooks.Open strFilename
End Sub

but I fear that’s too naive.

Reply | Quote

Hans & Legare,
I really appreciate you walking me through this. THANKS.
But….
Since starting this task I’ve been getting an error message indicating that
VBA6.DLL cannot be found and the macro will not run. When I’m in the VB
Editor I can run the macro but it appears the macro’s will only run from the
VB Editor screen. Prior to this venture all of the macro’s and buttons ran OK.
I have not added any new programs nor have I downloaded from the
internet. My virus software is up to date and I’ve gone through the Excel
Help Tools “Detect and Repair” process and the MS-Office cd repair
process to no avail. What did I do to deserve this ????
What would happen if I deleted the Excel PERSONAL.XLS file ? (just
a thought).

Reply | Quote

See if this newsgroup post helps, in particular the second problem + solution.

Reply | Quote

Amazing, simply Amazing.
That VBA6.DLL fix worked.
I would think there should/could be some type of cross reference in The Lounge to that fix.
Simply Amazing what gets answered in the Lounge !
Care to try and answer this one:
What are the 6-winning Ohio Lottery Numbers for the upcoming drawing. ?
Hans / Legare – Thanks ever so much.

Reply | Quote

> What are the 6-winning Ohio Lottery Numbers for the upcoming drawing. ?

Reply | Quote

To put “Something” into the first empty cell in column A, use something like this:

    With Worksheets("Sheet1").Range("A1")
        .Offset(.Range("A1").End(xlDown).Row, 0).Value = "Something"
    End With

To put “Something” into the cell after the last value in column A, use something like this:

    With Worksheets("Sheet1").Range("A1")
        .Offset(.Range("A65536").End(xlUp).Row, 0).Value = "My string"
    End With

To save the file using the name in cell A1, use something like this:

    If Worksheets("Sheet1").Range("A1").Value = "" Then
        MsgBox "No filename specified in Sheet1 cell A1."
        Exit Sub
    End If
    ThisWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A1").Value & ".xls"

To save a copy of the current file and have both the original and the copy open after the save, use the code that Hans posted.

Reply | Quote