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