Runtime error 1004 - Application-defined or object-defined error

Hi,

The following code was provided to me last night (thanks a bunch!!), but as I also need to identify which provider is responsible for the different sections that are listed, I have modified the sheets being copied to have the provider's name listed in column A for each row. When I changed the range to incorporate the changes I am now getting the following error:

Runtime error 1004 - Application-defined or object-defined error.

The line highlighted by the error is:

 Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Offset(, -1)

Another issue I have is the merged cells that are in the worksheets being copied. If I use the PasteValues option, I get an error that the merged cells must be the same size. I need to use paste values to pull the doctors name in as column A on the source worksheets has a formula to populate that.

the code now reads: I have also attached a revised version of the example worksheet
the code is listed in Module5
Example Worksheet-3.xlsm

Sub Billing_CopyIt()

    Dim ws As Worksheet
    Dim wsOut As Worksheet: Set wsOut = ThisWorkbook.Sheets("Billing Temp")
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
            Case Is = "Audit Temp", "Audit Summary", "Billing Temp", "Code_Table", "Master Provider List", "Accuracy Report Group", "Instructions"
                'do nothing
            Case Else
                On Error Resume Next
                Set rng = ws.Range("a23:j37").SpecialCells(2)
                On Error GoTo 0
                If Not rng Is Nothing Then
                    Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Offset(, -1)
                    rng.Copy: wsOut.Range("A" & Rows.Count).End(xlUp)(2, 1).PasteSpecial xlPasteAll
                    Set rng = Nothing
                End If
        End Select
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Any help is greatly appreciated

Carole