mercredi 23 août 2017

"Default range not applicable" error at second sheet / VBA

The concept is pretty basic, go create a summary sheet and start filling in data, and it does work on the first sheet (even though it takes an unreasonable amount of time), but then it then it throws the error "default range not applicable" at this line:

  If Len(c) <> 0 And InStr(1, c.Value, "C-") Then _

I am not sure what could change from one sheet to the next since they have the exact same structure.

'Add a worksheet with the name "Summary"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary"
DestSh.Range("A1").Value = "Account"
DestSh.Range("B1").Value = "Exchange"
DestSh.Range("C1").Value = "Quarter"
DestSh.Range("D1").Value = "Year"
Set destRng = DestSh.Range("A2")


'Loop through all worksheets and copy the data to the
'summary worksheet.
For Each xSheet In ActiveWorkbook.Worksheets

    If InStr(1, xSheet.Name, "ACCOUNT") And xSheet.Range("B1") <> "No Summary Available" Then _

        Set copyRng = xSheet.Cells

        For Each c In copyRng.SpecialCells(xlCellTypeVisible)

            If Len(c) <> 0 And InStr(1, c.Value, "C-") Then _


                'Copy to destination Range
                c.Copy destRng

                'Set destination
                Set destRng = destRng.Offset(1, 0)


            End If

        Next c

    End If

Next xSheet

Aucun commentaire:

Enregistrer un commentaire