Copy sheet with cube formulas to new book results in #NAME?

[semmio]

Hi!
all my previous question I found answers on on this forum, but on the following question I couldn't find any related topics:

I'm trying to write a VBA code to copy paste 2 sheets of my workbook to a new book. So far no problem with VBA code below:

Sub Button1_Click()
    With ThisWorkbook
    .Worksheets("budget 1").Unprotect
        .Worksheets("budget 1").Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        .Worksheets("budget 2").Unprotect
        .Worksheets("budget 2").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        .Worksheets("budget 2").Protect
        .Worksheets("budget 1").Protect
    End With
    Application.CutCopyMode = False
End Sub

BUT...
all the CUBEVALUE and CUBEMEMBER formulas in the original workbook result as #NAME ? instead of the values in the new book.

Anyone an idea how to copy paste the sheet values and keep original layout and formats like hidden columns, conditional formatting etcetera?

Thanks a lot!

 

[semmio]

UPDATE:

when I copy paste within the same workbook, the values of the cubevalue formula DO appear.

I don't exactly know what I'm doing in the VBA code, but this code below does the right thing, except from pasting the sheets to a new book.

Sub Button1_Click()
    With ThisWorkbook
    .Worksheets("budget 1").Unprotect
        .Worksheets("budget 1").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        .Worksheets("budget 2").Unprotect
        .Worksheets("budget 2").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        .Worksheets("budget 2").Protect
        .Worksheets("budget 1").Protect
    End With
    Application.CutCopyMode = False
End Sub

 

[semmio]

UPDATE: SOLVED

Below code first copies the sheets, then moves the new sheets to a new book. And that seems to work. I had to delete the button in the copied sheet though, otherwise it would error.

Sub Button1_Click()
    With ThisWorkbook
    .Worksheets("budget 1").Unprotect
    Application.CopyObjectsWithCells = False 
        .Worksheets("budget 1").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CopyObjectsWithCells = False
        ActiveSheet.Name = "budget 1 copy"
        
        .Worksheets("budget 2").Unprotect
        .Worksheets("budget 2").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        ActiveSheet.Name = "budget 2 copy"
        
   
        .Worksheets("budget 1").Protect
         .Worksheets("budget 2").Protect
         
      
      .Worksheets("budget 1 copy").Move
      .Worksheets("budget 2 copy").Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
    
                
             
    End With
   
End Sub

Feels like a workaround, so if someone has a more simple solution, please let me know!