• Excel ‘Save As’ cell contents (Excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Excel ‘Save As’ cell contents (Excel 2000)

    Author
    Topic
    WSNucthat1
    AskWoody Lounger
    February 24, 2005 at 10:04 pm #416327

    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
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      February 24, 2005 at 10:14 pm #931078

      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
      • WSNucthat1
        AskWoody Lounger
        February 25, 2005 at 7:47 am #931128

        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
        • WSHansV
          AskWoody Lounger
          February 25, 2005 at 8:15 am #931131

          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
          • WSNucthat1
            AskWoody Lounger
            February 25, 2005 at 10:35 pm #931280

            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
            • WSHansV
              AskWoody Lounger
              February 25, 2005 at 11:00 pm #931282

              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
            • WSNucthat1
              AskWoody Lounger
              February 27, 2005 at 2:56 am #931446

              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
            • WSHansV
              AskWoody Lounger
              February 27, 2005 at 10:26 am #931459

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

              Reply | Quote
            • WSNucthat1
              AskWoody Lounger
              February 27, 2005 at 1:47 pm #931471

              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
            • WSHansV
              AskWoody Lounger
              February 27, 2005 at 8:28 pm #931502

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

              blackteeth

              Reply | Quote
            • H. Legare Coleman
              AskWoody Plus
              February 25, 2005 at 11:23 pm #931285

              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
    Viewing 0 reply threads
    Reply To: Excel ‘Save As’ cell contents (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.