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

semmio

New Member
Joined
Aug 24, 2017
Messages
3
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:

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

New Member
Joined
Aug 24, 2017
Messages
3
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.

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

New Member
Joined
Aug 24, 2017
Messages
3
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.


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

Forum statistics

Threads
1,086,122
Messages
5,387,973
Members
402,091
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top