Case When error for set values

[Johnatha]

HI!

I'm having a weird problem.. I'm trying to look through a number of different columns for a certain value. If that value is there, then I want to copy over a certain range of cells into a new workbook. Some columns might contain values while other columns don't, and vice versa.

I've discovered my problem... If the value (example, 32) is NOT in the first column being searched, the rest of my case statements don't work, and I get the error "Run-time error '1004' Application-defined or object-defined error". See a sample of my code below (I have 20 cases total, and several lines of code before/after this; it's just a sample)

  Case 1
  Set NewBook = Workbooks.Add
  Workbooks.Open "M:\John\" & FName
    With Worksheets(WorkSheetPull)
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:=32
        If .AutoFilter.Range.Rows.Count > 1 Then
            .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1)
            .UsedRange.Columns(4).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4)
        End If
    End With
  
    Case 2
  Workbooks(FName).Activate
    With Worksheets(WorkSheetPull)
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=6, Criteria1:=32
        If .AutoFilter.Range.Rows.Count > 1 Then
            .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1)
            .UsedRange.Columns(6).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4).End(xlDown).Offset(1)
        End If
    End With
    
    Case 3
  Workbooks(FName).Activate
    With Worksheets(WorkSheetPull)
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=8, Criteria1:=32
        If .AutoFilter.Range.Rows.Count > 1 Then
            .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1, 0)
            .UsedRange.Columns(8).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4).End(xlDown).Offset(1, 0)
        End If
    End With

If I set my criteria to a number that IS in the 1st column (example, 24) then it works!

I'm realy stuck, any thoughts would be helpful!

Thanks.

[mancubus]

hi.

NewBook is set only in Case 1.
if it will be used for all cases, move it above Case 1 line.
otherwise copy and paste that line after relevant case lines.

[SamT]

I think you can move all the below to before the Select Case

Set NewBook = Workbooks.Add 
    Workbooks.Open "M:\John\" & FName 
    With Worksheets(WorkSheetPull)

[Johnatha]

Hi mancubus, SamT,

Thanks for the help but that still doesn't solve my problem

Even when I put that line before the case statement, for certain integer values (example, 32) it always gives me that error message on "Case 2" at line " .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1)"

The only thing I can think of why is because the values (example, 32) are NOT in the first column, or the first Case statement. Any thoughts would help out a ton.

Thanks!

[mancubus]

why not post entire code and workbook here?

[SamT]

I wonder why people ALWAYS leave out the most important part of the code. In this case, the code that sets the Select Case variable variable.

I see the problem! In every case the criteria is hardcoded to "32," there fore you must insure that in every case the column has "32" in it.

Case Solved

I am totally guessing about what code you have and what is raising the error, but...

    Set NewBook = Workbooks.Add
    Workbooks.Open "M:\John\" & FName
    With Worksheets(WorkSheetPull)
        .AutoFilterMode = False

 Select Case Johnatha
Case 1
        If Not .Columns(4).Find(32) Is Nothing Then
        .Cells(1).AutoFilter Field:=4, Criteria1:=32
        If .AutoFilter.Range.Rows.Count > 1 Then
            .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1)
            .UsedRange.Columns(4).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4)
        End If
    End If
Case 2
If Not .Columns(6).Find(32) Is Nothing Then       
'Etc