Forum Discussion
Errors when coying to another workbook
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 workb...
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
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- 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
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 SubThis manages the pivot table.
