Errors when coying to another workbook

[TotallyWired]

Hello,
Let me explain my problem.

I have created a cost sheet for my restaurant that works quite well, with the help of forums and ChatGPT. I'm just a chef so be gentle 

In the first workbook, "Costes", I do the cost calculation, and once finished I copy it to the "escandallo" book using a macro button.

The problem is that the macros from "Costes Esferic" are also copied, which causes errors. I would like only the sheet with the formulas to be copied, without the macros, and for it to be complete.

COSTES 
ESCANDALLO 

Any help will be greatly appreciated.

Cheers,
R

[#2]

It says second file ESCANDALLO has deleted.

[#3]

@Logaraj Sekar 

Thank you for your reply.

My bad, I moved them by mistake and I have re-uploaded them. WORKSHEETS 

Below you see the code giving me error, line 5, I think it must be looking for a pivot table that is not there, but that code should not be there anyway.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shtP As Worksheet           ' Pivot worksheet

' initalize variables
Set shtP = Sheets("Pivot")

' Only consider columnm B
If Intersect(Target, Range("B10:B29")) Is Nothing Then Exit Sub

' Set the selected provider
shtP.Range("Sel_Pro") = Target.Offset(0, -1).Value

' Check to make sure it is in the database
If shtP.Range("Num_Ref") = 0 Then
    MsgBox "Selecciona Proveedor"
Exit Sub
End If

' Set the fileter
shtP.Range("Filter_Pro") = shtP.Range("Sel_Pro")

' Refresh the pivot tables
shtP.PivotTables("Pt_referencia").PivotCache.Refresh
   

End Sub

I am able to get rid of the error if a save Escandallo as macro-free book but I would like to be able to copy the cost without any VBA/macro in it. 

Is this a matter of where the code is located? VBAProject (COSTES.xlsb)/Objects/Sheet1

To be honest,  I have no clue.

Cheers!

[#4]

Have you changed any of your workbook sheet name 'PIVOT' to any name?

[#5]

No, pivot is a sheet on "COSTES".
When I make the copy to "Escandallo" the code gets copied as well and gives me error as there's no shuch sheet on "Escandallo"

What I would like is to be able copy just the sheet with format, formulas, etc, not the code

[#6]

The code behind the macro button that copies "Costes" to "Escandallo" book needs to be fixed. Thanks

[#7]

Yes, any idea how to fix it?

[#8]

Show me the code

[#9]

I already read all previous posting and I understand this already. Please share the code behind the macro button that copies the workbook. To see the code just right click the button nd then choose "Assign Macro..." menu. In the resulting dialog press "Edit" button. You will be presented with a block of code. Just copy the block of code starting with "Sub" and ending with "End Sub". Share the code with me. I hope I can fix this. I belive the code should not expose anything confidential..Thanks

[#10]

@sanjibdutta here goes the code. Thank you for your time!

Sub CopyRecipe()
    On Error Resume Next
    
    Dim newName As String
    newName = Sheets("Plantilla Coste").Range("B2").Value
    
    ' Check if worksheet with same name exists
    Dim sheetExists As Boolean
    sheetExists = False
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name = newName Then
            sheetExists = True
            Exit For
        End If
    Next sh
    
    If sheetExists Then
        MsgBox "La receta " & newName & " ya existe. Por favor, elije otro nombre."
        Exit Sub
    End If
    
    ' Copy worksheet
    Dim destWb As Workbook
    Set destWb = Workbooks("Escandallo.xlsx")
    Sheets("Plantilla Coste").Copy After:=destWb.Sheets(destWb.Sheets.Count)
    
    ' Rename worksheet
    destWb.Sheets(destWb.Sheets.Count).Name = newName
    
    ' Delete data validation from range A10:B29
    Dim firstValidationCell As Range
    Set firstValidationCell = destWb.Worksheets(newName).Range("A10:B29").Find(What:="*", LookIn:=xlValidation)
    If Not firstValidationCell Is Nothing Then
        firstValidationCell.Resize(20).Validation.Delete
    End If
    
    ' Delete buttons
    destWb.Worksheets(newName).Shapes.Range(Array("Button 1", "Button 2")).Delete
    
    Range("B2").Select
    
    On Error GoTo 0
End Sub

[#11]

Thank you. Is this the whole code behind macro button? But this code does not save new workbook "Escandallo.xlsx"("destWb") or it does not create it ( interesting the workbook has xlsx extension that should not contains any VBA code). I need to see the part of code which saves this new workbook or create this new workbook. If you are not sure which part is that then can you please show me whole code from the editor where you cipied this part of code from. Sorry..it is difficult until I see the whole code. What I would try to fix is that I would modify the code where it is saved or created..Thanks

[#12]

@sanjibdutta 

There are 2 books opened at the same time, one in COSTES(cost) and the other ESCANDALLO(dish cost) which is empty. The code does not create a new book, it only creates a copy of PLANTILLA(template) sheet the from COSTES to ESCANDALLO book.

I can get rid of the macros if I save it as .xlsx but I still get error because of the following code being copied:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shtP As Worksheet           ' Pivot worksheet

' initalize variables
Set shtP = Sheets("Pivot")

' Only consider columnm B
If Intersect(Target, Range("B10:B29")) Is Nothing Then Exit Sub

' Set the selected provider
shtP.Range("Sel_Pro") = Target.Offset(0, -1).Value

' Check to make sure it is in the database
If shtP.Range("Num_Ref") = 0 Then
    MsgBox "Selecciona Proveedor"
Exit Sub
End If

' Set the fileter
shtP.Range("Filter_Pro") = shtP.Range("Sel_Pro")

' Refresh the pivot tables
shtP.PivotTables("Pt_referencia").PivotCache.Refresh
   

End Sub

This manages the pivot table.

[#13]

Two things...1)Once PLANTILLA(template) sheet is copied from COSTES to ESCANDALLO book the ESCANDALLO book must be saved in some code to save the changes.where is that code?
2)When a sheet containing a button with code behind is copied to another even macro free book (xlsx) the VB code gets copied anyway. In this situation the best is to avoid macro button driven code in COSTES and just copy sheet and save ESCANDALLO by code. Instead of cycling back on the issie I can fix this once for all if you share the COSTES workbook and ESCANDALLO workbook(if it is not realy blank workbook)..Of course if you don't have any issue sharing this. You can send workbook to my email address at email address removed for privacy reasons

[#14]

Thank you very much.

Here are the books BOOKS 

[#15]

@TotallyWired 

Sorry..I had to be a little tied up for the delivery of one of my assignment. Check this workbook if this works for you. Not sure if it would work or generate error. I fixed something based on some assumtion of your environment and process which is not revealed from the workbook you provided..Thanks