May 12 2023 10:21 AM
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
May 12 2023 08:30 PM
May 13 2023 04:39 AM - edited May 13 2023 05:48 AM
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!
May 13 2023 06:26 AM
May 13 2023 07:00 AM
May 13 2023 12:01 PM
May 14 2023 02:27 AM
May 14 2023 02:54 AM
@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
May 14 2023 03:31 AM
May 14 2023 03:48 AM
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.
May 14 2023 04:37 AM - edited May 14 2023 04:43 AM
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
May 14 2023 04:44 AM
Thank you very much.
Here are the books BOOKS
May 14 2023 12:28 PM
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