Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro to save file based on cell value

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to save file based on cell value

    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?
    This macro is amazingly straight-forward:

    Public Sub SaveAsA1()
    ThisFile = Range("A1").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    End Sub
    I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's
    T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

    Any help appreciated..

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Macro to save file based on cell value

    Try

    Code:
    Public Sub SaveAsA1()
    ActiveWorkbook.SaveAs Filename:="T:\COMMISSIONING\IJT\TIMELOG project\Staff#1\" & Range("A1").Value
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Jun 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to save file based on cell value

    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.......

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Macro to save file based on cell value

    Can you navigate to that path manually - i.e. File > Save As.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to save file based on cell value

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •