Vba code works at times, and at times fails miserably

Question

I have written a pretty elaborate code to automate 2 hours of work into less than 1 min. It works, on most days, somedays like today, the code wont work, or parts of the code wont work. Its the most obviously simple parts of the code that doesn't work. This is frustrating me. To get the code to work, what I have to do would be to restart the system.

Please understand, I dont change the code at all. either before or after the error happens.

the is the code, where the error happens.

Range("Table1_2[[#Headers],[Bag No]:[Batch Making]]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Batch Making").Delete
Sheets.Add(After:=Sheets("Sheet1")).Name = "Batch Making"

Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit

Today the error was that it would not paste what the code had selected. please note:

1. It selected the set of rows and columns
2. It created the new sheet and selected the first cell
3. It tried pasting the value also, but nothing happened.

After restarting the system, code worked like a dream.

Why does this happen?? any clue ??

EDIT: Biggest issue is replicating the error, as I mentioned on some days, the code will crash, else it will run smoothly.

Every day new data is fed to the program, and its cleaned to ensure that only what the program can take is given to it and this involves removing #N/A's, #VALUE's and #Ref (this was done today also, I double checked the data after the crash)

Yet at times it fails.

I'll remove the Error Handlers and have separate code to check for availability of sheet, incase the error pop's up again, then I'll update here.

Answer 1

You can try below code. Using select is not the best idea in 99% of time. Also, when referencing a cell it is always good to precisely tell VBA which worksheet to use.

I would suggest to replace on error resume next clause - it disables all errors in your code and you will not be notified if something went wrong.In this case you can write a bit more code to check whether the specific worksheet exist and then remove/add it.

Sub copytable()
        
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("worksheet_with_table_you_want_to_copy")
        
        On Error Resume Next
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets("Batch Making").Delete
        ThisWorkbook.Sheets.Add(After:=Sheets("Sheet1")).Name = "Batch Making"
        Application.DisplayAlerts = True

        ws.ListObjects(1).Range.Copy
        With ThisWorkbook.Worksheets("Batch Making")
            .Range("a1").PasteSpecial xlPasteAll
            .Cells.EntireColumn.AutoFit
        End With
    End Sub

edit: Code without on error but with check wheather worksheet "Batch Making" exists and delete it if it's true

Sub copytable()
        
    Dim wsTable As Worksheet
    Set wsTable = ThisWorkbook.Worksheets("worksheet_with_table_you_want_to_copy")
    Dim ws As Worksheet
    
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Batch Making" Then ThisWorkbook.Worksheets("Batch Making").Delete
    Next ws
    Application.DisplayAlerts = True
    ThisWorkbook.Sheets.Add(After:=Sheets("Sheet1")).Name = "Batch Making"
    ws.ListObjects(1).Range.Copy
    With ThisWorkbook.Worksheets("Batch Making")
        .Range("a1").PasteSpecial xlPasteAll
        .Cells.EntireColumn.AutoFit
    End With
End Sub

Answer 2

Unfortunately, you don't say what your code is supposed to do but it seems that you have a template and try to create a new worksheet with a table from this template.

The problem is that the source isn't qualified. Your code copies a "Table_2" from an unspecified source sheet, then deletes the "Batch Making" tab, creates a new sheet by that name and pastes the clipboard to this new sheet. The process is slightly illogical because if the source is a sheet by the name of "Batch Making" then it takes a lot of confidence to delete the original before the copy is safely in place. But I think you have a template. Therefore there is no danger of losing data but the question arises why you make the copy before you need it. Either way, if the copy on the clipboard is lost while deleting and inserting sheets "nothing will happen", as you say.

But what should happen? I presume that you just get a fresh copy of the template, and that means that you retain the structure of the table. Therefore my code below takes a radically different approach. It doesn't delete a sheet (with the table in it) and doesn't create a new sheet. Instead it simply deletes the content of the table in the existing sheet.

Sub Snippet()
    ' 295

    Const WsName    As String = "Batch Making"

    Dim Ws          As Worksheet            ' Sheets(WsName)
    Dim Tbl         As ListObject
    
    On Error Resume Next
    Set Ws = Worksheets(WsName)
    If Err Then
        MsgBox "Sorry, there is no tab """ & WsName & """ in this workbook.", _
               vbInformation, "Missing worksheet"
    Else
        Set Tbl = Ws.ListObjects(1)
        If Err Then
            MsgBox "Sorry, there is no table on the specified worksheet.", _
                   vbInformation, "Missing table"
        Else
            Tbl.DataBodyRange.ClearContents
        End If
    End If
End Sub

This is done by a single line of code, to wit, Tbl.DataBodyRange.ClearContents. You could take more or different action at that point. For example, you could delete unwanted rows or add default cell content. If there is a lot of such default you might get those data (or formulas) from a template. Otherwise just add it to the code. Headers and totals aren't deleted. The table's structure remains unchanged.

Before that, in the above code, the sheet is qualified and the table. In place of the error messages you could insert code to create the tab and/or create the table.

Please observe the name of the worksheet at the top of the code. You can change that string to any other name. The code doesn't specify the name of the table. Instead, it presumes that there is only one table on that sheet and clears that one.