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