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

mummo

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hi
try following & see if helps

Place this code in standard module

VBA Code:
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


Rich (BB 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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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

1617792858531.png


this will help understand what your RefersTo references look like

Dave
 

mummo

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.
name manager test.PNG
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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
Rich (BB code):
BreakAllLinks NewWorkBook

Dave
 

mummo

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Rich (BB code):
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.
 
Last edited by a moderator:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Curious,
do you need to keep all those names in the copied workbook for it to do what you want?

Dave
 

mummo

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

Rich (BB code):
   DeleteBadNames NewWorkBook
   BreakAllLinks NewWorkBook
  
    NewWorkBook.SaveAs FilePath & FileName, 51

Run main code & let me know outcome

Dave
 

mummo

New Member
Joined
Apr 2, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
OK,
step at a time

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

VBA Code:
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

Rich (BB 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

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top