Johnatha
10-23-2014, 11:41 AM
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.
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.