VBA to save a copy with cell value name and chosen sheets

[mummo]

Hello!
I am pretty new to VBA an this is my first post here.
My problem is that I am trying to make a save button in file that has multiple sheets. I need it to save a copy of the open workbook in xlsx-format, with a name from A1 and remove one sheet from the copy. I have it working, but it flashes the excel on the screen before saving. It should save it in the background, not open it and keep the current workbook open without saving anything to the active workbook.
This is the current code:

Sub SaveOma()

Dim FileName As String
Dim Path As String
Dim NewWorkBook As Workbook
Dim OldWorkBook As Workbook
Set NewWorkBook = Workbooks.Add
Set OldWorkBook = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Path = "C:\Users\Tomi\Desktop\"
FileName = OldWorkBook.Sheets("Checklist and decision").Range("A1").Value & ".xlsx"

Dim x As Integer

For x = 2 To OldWorkBook.Worksheets.Count
OldWorkBook.Worksheets(x).Copy after:=NewWorkBook.Worksheets(NewWorkBook.Worksheets.Count)
Next x

NewWorkBook.Worksheets(1).Delete

NewWorkBook.SaveAs Path & FileName
NewWorkBook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Is there anything to add or improve so it would run more smoothly?

 

[mummo]

Hi
try following & see if helps

Place this code in standard module

Sub BreakAllLinks(ByVal wb As Object)
    Dim MyLinks     As Variant
    Dim i           As Long
    With wb
        MyLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
        If IsArray(MyLinks) Then
            For i = LBound(MyLinks) To UBound(MyLinks)
                .BreakLink Name:=MyLinks(i), _
                           Type:=xlLinkTypeExcelLinks
            Next i
        End If
    End With
End Sub

Then in your main code add the following line just before the SaveAs line of code

   BreakAllLinks NewWorkBook
  
    NewWorkBook.SaveAs FilePath & FileName, 51

Dave

Thank you again!
It does remove the link between the copy and my original workbook, but it also seems to break cell references between sheets within the new copy.
Do you know if this can be fixed or would it be easier to go back to simpler code? This currently is way beyond my skillset and I really appreciate all the help, I can't do anything to it myself because I don't know how to.

 

[dmt32]

No really sure what you mean by "go back to simpler code"? If you had something that was doing what you want then yes use that.

If still having issues then after making copy of worksheets to new workbook, post an image of the name manager like below

this will help understand what your RefersTo references look like

Dave

 

[mummo]

By simpler I only meant doing most steps manually like before, I would not want to do that but I am not skilled enough to fix these issues by myself.

This is the name manager. The references between different sheets disappear in the copy. For example I have a sheet named "Overview and comments"with cell E83 that should reference to "='Risk and LTV'!F8" but it only copies the value and the reference disappears.

 

[dmt32]

Hi,
sorry,
should have stated to to run the main code without the new line of code to break the links

remove this line, run the code & then post screenshot of the Name Manager

BreakAllLinks NewWorkBook

Dave

 

[mummo]

Hi,
sorry,
should have stated to to run the main code without the new line of code to break the links

remove this line, run the code & then post screenshot of the Name Manager

BreakAllLinks NewWorkBook

Dave

Ok great, I did it again but to me it seems the same. The cell references show that they are connected to the original workbook. When I have a cell that should copy the value from a cell in another sheet in the same workbook, now it tries to copy the value from the right sheet and cell but the wrong workbook.

EDIT:
Image removed at OP's request.

 

[dmt32]

Curious,
do you need to keep all those names in the copied workbook for it to do what you want?

Dave

 

[mummo]

Curious,
do you need to keep all those names in the copied workbook for it to do what you want?

Dave

Honestly I highly doubt it. This is work excel that has had multiple different editors and versions. If there is something obvious to you that might cause these problems, I am more than willing to delete those.

 

[dmt32]

Honestly I highly doubt it. This is work excel that has had multiple different editors and versions. If there is something obvious to you that might cause these problems, I am more than willing to delete those.

OK,
step at a time

Place this code in same module as the Break Links code I provided earlier

Sub DeleteBadNames(ByVal wb As Object)
  Dim nm As Name
  For Each nm In wb.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
  Next nm
End Sub

Then in your main code you need following two lines of code just before the SaveAs code

   DeleteBadNames NewWorkBook
   BreakAllLinks NewWorkBook
  
    NewWorkBook.SaveAs FilePath & FileName, 51

Run main code & let me know outcome

Dave

 

[mummo]

OK,
step at a time

Place this code in same module as the Break Links code I provided earlier

Sub DeleteBadNames(ByVal wb As Object)
  Dim nm As Name
  For Each nm In wb.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
  Next nm
End Sub

Then in your main code you need following two lines of code just before the SaveAs code

   DeleteBadNames NewWorkBook
   BreakAllLinks NewWorkBook
 
    NewWorkBook.SaveAs FilePath & FileName, 51

Run main code & let me know outcome

Dave

Tried it and everything else is working great, but still the references between sheets get copied as values. All the cell references within sheets remain working, but if there is a reference between sheets it changes to value only.

Really appreciate all the time and effort you have put in to this!

 

[dmt32]

Tried it and everything else is working great, but still the references between sheets get copied as values. All the cell references within sheets remain working, but if there is a reference between sheets it changes to value only.

Sounds like progress

Next step

from your master file turn the macro recorder on & then open the name manager.
Select in turn from the list, only the names that are required in the copied workbooks.
With each selection, press the Edit Button & when the Edit Name dialog appears, just press the OK button.

When all done, close the name manager & turn macro recorder off.
copy the recorded code & post it here. You can then delete the recorded code from your file.

Dave