0

I have an extensive bit of code that is processing a report of raw data into usable worksheets. I'm getting the 1004 error toward the end. The code is filtering data on Sheet 2, copying it, pasting it to a new sheet tab, and is then going back and deleting out the filtered data, and then repeats for several more tabs. It's running fine for the first 3-4 filter criteria, but then failing on the last one. I'm lost as it's the same code it's already run without incident.

I've tried rewording how I define my range initially. No luck. I've tried changing up the order of the code, still no luck.

DIM rng As Range (defined in Private Sub CommandButton1_Click())

Module 1 Code
.
.
.
Selection.End(xlDown).Select  'where I define rng
ActiveCell.Offset(0, 12).Select
Set rng = ActiveCell
Range("A2", rng).Select
.
.
.
'Creates GroupB tab WORKS FINE
    ActiveSheet.Range("$A$1:$N$192").AutoFilter Field:=5, Criteria1:="GroupB"
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Range(Range("A2"), rng).SpecialCells(xlCellTypeVisible).EntireRow.Delete

'Creates GroupC tab WORKS FINE
    ActiveSheet.Range("$A$1:$N$192").AutoFilter Field:=5, Criteria1:="GroupC"
    Range("A1", rng).Select
    Selection.Copy
    Sheets("Sheet3").Select
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Range(Range("A2"), rng).SpecialCells(xlCellTypeVisible).EntireRow.Delete

'Creates GroupD tab WORKS FINE
    ActiveSheet.Range("$A$1:$N$192").AutoFilter Field:=5, Criteria1:="GroupD"
    Range("A1", rng).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Range(Range("A2"), rng).SpecialCells(xlCellTypeVisible).EntireRow.Delete

'Creates GroupE tab 

    ActiveSheet.Range("$A$1:$N$192").AutoFilter Field:=5, Criteria1:="GroupE"

    Range("A1", rng).Select *Fails HERE with the run time error 1004 vba method range object _global failed Error

    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet5").Select
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Range(Range("A2"), rng).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Please I don't know why it works fine for GroupB-D, but not E, it's the SAME code.

Ian Crowley
  • 1
  • 4
    I **highly** recommend that you [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). If you follow what is mentioned there and work with objects and handle them correctly then I am sure you will not get the error that you are getting. – Siddharth Rout Aug 04 '19 at 16:50
  • 3
    You're doing a lot of `Delete` operations, and although you're not explicitly "iterating", you're basically doing an iteration. It's generally a bad idea to remove/delete items from a collection/range/etc. during iteration. It's possible that the GroupD Delete call has invalidated the `rng` object variable. – David Zemens Aug 04 '19 at 17:20
  • Thanks I'll check out that link to avoid selecting. I'm basically self-taught via Google on how to create/write VBA code, and while maybe not the cleanest or best solution, I've usually found a way around errors, hiccups, and challenges to getting what I want it to do. This is is hard because I need to include columns that have blank cells in my selected range, which is always changing whenever the report needs to be run. – Ian Crowley Aug 05 '19 at 18:04

0 Answers0