Ignoring Worksheet Copy Error

mochalatte

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi, I obtained this code for get individual worksheet sizes inside the workbook but it produces errors when attempting to copy a worksheet with vba code defining a global variable such as
VBA Code:
Private timerDictionary As Scripting.Dictionary

This is the code I used to get individual size of each worksheet and store it into a worksheet on same workbook called "Settings". The code works but produces User Defined Type Errors. I want to ignore these error messages but I could not come up with a way to do it. I tried disabling alerts and background checking but to no avail. I dont wish to use On Error Resume Next since it is still working as expecting. Is there a way I can just ignore the error messages prompted. The error occurs when
VBA Code:
xWs.Copy
is executed. It only happens for certain worksheets containing a Scripting Dictionary. I am using Microsoft Excel 365. Any help would be greatly appreciated.

VBA Code:
Sub WorksheetSizes()
'Update 20140526
Dim xWs As Worksheet
Dim Rng As Range
Dim xOutWs As Worksheet
Dim xOutFile As String
Dim xOutName As String
xOutName = "Settings"
xOutFile = ThisWorkbook.Path & "\TempWb.xls"
On Error Resume Next
Application.DisplayAlerts = False
Application.ErrorCheckingOptions.BackgroundChecking = False
Err = 0
Set xOutWs = Application.Worksheets(xOutName)
If Err = 0 Then
    'xOutWs.Delete
    Err = 0
End If
'With Application.ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
'    .name = xOutName
'    .Range("TabSizeStart").Resize(1, 2).value = Array("Worksheet Name", "Size")
'End With

Set xOutWs = Application.Worksheets(xOutName)
Application.ScreenUpdating = False
xIndex = 1
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.name <> xOutName Then
        If (xWs.name <> "Trading") Then
            xWs.Copy
            Application.ActiveWorkbook.SaveAs xOutFile
            Application.ActiveWorkbook.Close SaveChanges:=False
            Set Rng = xOutWs.Range("TabSizeStart").Offset(xIndex, 0)
            Rng.Resize(1, 2).value = Array(xWs.name, VBA.FileLen(xOutFile))
            Kill xOutFile
            xIndex = xIndex + 1
        End If

    End If
Next
Application.ScreenUpdating = True
Application.Application.DisplayAlerts = True

End Sub
P.S. I have Microsoft Sciprting Runtime, Microsoft Scriplet Library and Microsoft Office 16.0 Object Library enabled under Tools/ References. I have tried disabling display alerts and background checking but I still get erros when trying to Copy the worksheet.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top