Forum Discussion

TotallyWired's avatar
TotallyWired
Copper Contributor
May 12, 2023

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...
excel
Macros and VBA
TotallyWired's avatar
TotallyWired
Copper Contributor
May 14, 2023

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
sanjibdutta's avatar
sanjibdutta
Brass Contributor
May 14, 2023
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
  • TotallyWired's avatar
    TotallyWired
    Copper Contributor
    May 14, 2023

    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.

     

     

     

    • sanjibdutta's avatar
      sanjibdutta
      Brass Contributor
      May 14, 2023

      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

      • TotallyWired's avatar
        TotallyWired
        Copper Contributor
        May 14, 2023

        Thank you very much.

        Here are the books https://www.dropbox.com/sh/wyb8f3mxydb2di4/AAAlET7xkWb84K7zeMQxER02a?dl=0 

Resources