Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: Save a copy of a workbook with a new name
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    just replace "[book1]" with the name of the original workbook 1

    Code:
    Sub referenceFIX()
    Dim wsCOUNT As Long
    Dim i As Long
    Dim newWB As String
    
    newWB = InputBox("What is the name of the new workbook?")
    wsCOUNT = ActiveWorkbook.Worksheets.Count
    
    For i = 1 To wsCOUNT
    ActiveWorkbook.Worksheets(i).Cells.Replace What:="[" & newWB & "]", Replacement:="[Book1]", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next i
    
    
    End Sub
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  2. #12
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    I tried the macro you wrote. It asked me for the name of a new file, which I entered, but I can't find that file saved anywhere. You asked that I put the name I want in [book1] in the macro. I put the name of the workbook "ALL-IN-1 CALCULATOR 2019 with PTD.xlsm". That is the name of WB1 and it is a Read-Only file.

    Trial 1) I ran the macro from within WB1. It asked for a name so I named it runfromWB1.xlsm. Then the macro simply ended. I checked Documents, My Documents, Public Documents, and the original location of WB1, but find no file.

    Trial 2) I ran the macro from within WB2, naming it runfromWB2.xlsm, and got the same result.

    Trial 3) I first SaveAs WB1 as Test Save.xlsm. My open document now says Test Save.xlsm and my cell references in WB2 all reference Test Save.xlsm. I ran the macro from within Test Save.xlsm. The macro asked me for a new name, so I tried runfromRenamedWB.xlsm and the macro ended. I still have Test Save.xlsm and WB2 references Test Save.xlsm.

    Trial 4) I first SaveAs WB1 as Test Save.xlsm. Again, my open document now says Test Save.xlsm and my cell references in WB2 all reference Test Save.xlsm. I ran the macro from within WB2. I got the same result as Trial 3.

    Trials 5 and 6 involved using the Test Save.xlsm name in the dialog box. Trial 5 was run from within Test Save.xlsm. The macro did nothing. Trial 6 was run from within WB2. This time I got a different result. After entering Test Save.xlsm in the dialog box, the macro opened up a directory for me to find a file. It did this three times, once for each find of the Test Save.xlsm reference "in brackets". It did not replace the references to named ranges that didn't have brackets. It also left WB2 referencing a document that was no longer in Excel because it was saved with a new name.

    I'm not an expert with macros, but this doesn't appear to do what I need. If I'm understanding this right, even if I get the macro to change all the references back, I still need to open an original WB1 again, which is one of the things I'm trying to avoid.

    I only want to save a copy of WB1 in the background without changing the name of the worksheet currently displayed. WB2 is not relevant to the copy. This macro you've written is specific to only fixing WB2. If I can get a copy of WB1 made, there will be no reason to fix WB2. A macro that saves a copy would also be quite a bit more useful as it would work on any Excel workbook.

    I appreciate you trying to help, but it appears you are steering me in a direction in line with your impression of what I should need. I just want to save a copy of a workbook to a folder for future use. I need to leave the original upfront and unchanged while I am working.

    VBA even has a function SaveCopyAs, but I can't get it to work properly. I got it to save files that couldn't be opened. Can we pursue what I posted earlier and see if that bears any fruit? From what I've read online, similar things are being done but it's all just a hair beyond my level of knowledge.
    Last edited by AndyTampa; Aug 15th, 2019 at 06:31 PM.

  3. #13
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    I still need to open an original WB1 again, which is one of the things I'm trying to avoid.
    then what is the file path for the ALL-IN-1 CALCULATOR 2019 with PTD.xlsm
    i.e c:/users/desktop/folder/
    Last edited by BlakeSkate; Aug 16th, 2019 at 08:22 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  4. #14
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    With the original workbook 1 being closed
    The copy of workbook 1 with the new name open and saved
    and the 2nd workbook (where the macro will be located in) open

    try this code:

    Code:
    Sub referenceFIX()
    Dim wsCOUNT As Long
    Dim i As Long
    Dim newWB As String
    
    newWB = InputBox("What is the name of the new workbook?")
    wsCOUNT = ActiveWorkbook.Worksheets.Count
    
    For i = 1 To wsCOUNT
    ActiveWorkbook.Worksheets(i).Cells.Replace What:="[" & newWB & ".xlsm]", Replacement:="C:\YOUR PATH HERE\[ALL-IN-1 CALCULATOR 2019 with PTD.xlsm]", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next i
    
    
    End Sub
    note:
    replace "YOUR PATH HERE" with the file location that should look something like C:\Users\whatever\Desktop\folder or just comment it and i'll update the code
    when typing the name of the new workbook just write the name, and not the file extension (i updated it) given that its an xlsm format
    Last edited by BlakeSkate; Aug 16th, 2019 at 08:30 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #15
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    That's not going to work at all for me.

    Quote Originally Posted by BlakeSkate View Post
    With the original workbook 1 being closed
    Workbook1 will not be closed. The whole point is to not have it close or be renamed. I only want to save a copy of Workbook1 when I need it without using the SaveAs command. It's the SaveAs command which changes the name of the workbook and breaks the formula references in the other workbook.

    Quote Originally Posted by BlakeSkate View Post
    The copy of workbook 1 with the new name open and saved
    I don't need the copy left open. I need the original left open and unchanged. I need the copy with the new name closed and saved.

    Quote Originally Posted by BlakeSkate View Post
    and the 2nd workbook (where the macro will be located in) open
    The macro will not be located in the 2nd workbook. It will be located in my PERSONAL macros workbook. When it does what I'm asking, it will be useful for more than this single workbook.

    This whole process of breaking the formulas in the other workbook so they can be fixed is more complicated than just preventing the break in the first place. I don't need a macro to fix Workbook2. It's not the problem.

    The problem, for which I am here seeking a solution, is how to save a copy of the currently displayed workbook in the background under a new name without affecting the currently displayed workbook or it's current name.

  6. #16
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    After all this, a simple line works to create a copy without breaking references.
    Code:
    ActiveWorkbook.Sheets.Copy
    I still have to manually save as a different name, but the references aren't pointing to it, so I get exactly what I'm asking for. It would be so much better if it would ask me for a name though and save it, which is what I really wanted, but this is 75% there.

  7. #17
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    It would be so much better if it would ask me for a name though and save it, which is what I really wanted,
    let me know if this works for you then.
    Code:
    Sub referenceFIX()
      Dim newWB As String
      Dim fold As String: fold = "C:\filepath\"
      
    
    ActiveWorkbook.Sheets.Copy
    newWB = InputBox("Enter a save name")
      With ActiveWorkbook
        .SaveAs fold & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close False
      End With
    
    End Sub
    change filepath to your save destination
    remove "& Format(Date, "MM-DD-YYYY")" if you do not wish to include a date of your file save
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  8. #18
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    That works perfectly. I think I can edit the InputBox to add instructions such as "don't include the extension" or something else, but it does exactly what I wanted. Thank you.

    I have a question though about the statement ".Close False". The macro closes the saved workbook, which is exactly what I want, but the statement appears to tell the macro to leave the workbook open. Is that just my lack of knowledge of VBA or is it supposed to close it?

  9. #19
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    The macro closes the saved workbook, which is exactly what I want, but the statement appears to tell the macro to leave the workbook open. Is that just my lack of knowledge of VBA or is it supposed to close it?
    the .Close closes the document
    the "false" answers the command prompt to save your current document as "no"
    So if you were to click the x at the top right of excel it would ask if you wanted to save your sheet before closing. its basically clicking the don't save changes for you.
    It is also setting a false value to filename change and routing the file.
    Last edited by BlakeSkate; Aug 20th, 2019 at 11:02 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  10. #20
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    So I'd want the "false" because we already saved the document. Interesting. Thank you.

Some videos you may like

User Tag List

Tags for this Thread

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
  •