| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am pulling my hair out
Hi Everyone
I have this code that filters 'non-empty cells' in 4 different sheets from A3 to last row then paste into sheet5. I think majority of code is doing what it is suppose to be doing except last one inside of my For ~ Next statement. As you can see Sheet1,2,3 and 4 gets filtered and results gets pasted into sheet5. It is suppose to paste the result then find the lastrow + 1 then paste and so on but It seems it is not doing that. Can anyone help? Thank you! Regards James Here is the part of the code Application.ScreenUpdating = False LastRange = Sheets("Sheet5").Range("A65000").End(xlUp).Row + 1 shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") With Sheets("Sheet5") Set CopyToRange = .Range("A" & LastRange) End With For sh = 0 To UBound(shArray) Sheets(shArray(sh)).Activate LRR = Range("A3").End(xlDown).Row Set FilterRange = Range("A3", Cells(LRR, "C")) FilterRange.AutoFilter field:=2, Criteria1:="<" FilterRange.Copy Destination:=CopyToRange FilterRange.AutoFilter ' Problem seems to start here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! With Sheets("Sheet5") Set CopyToRange = .Range("A3").End(xlDown) End With Next Application.ScreenUpdating = True |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I am pulling my hair out
Not tested, but maybe
Application.ScreenUpdating = False shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") For sh = 0 To UBound(shArray) With Sheets("Sheet5") LastRange = .Cells(.Rows.Count, "A6").End(xlUp).Row + 1 Set CopyToRange = .Range("A" & LastRange) End With With shArray(sh) LRR = .Range("A3").End(xlDown).Row Set FilterRange = .Range("A3", .Cells(LRR, "C")) FilterRange.AutoFilter field:=2, Criteria1:="<" FilterRange.SpecialCells(xlCellTypeVisible).Copy Destination:=CopyToRange FilterRange.AutoFilter End With Next Application.ScreenUpdating = True -- __________________________________ HTH Bob "James8309" wrote in message ... Hi Everyone I have this code that filters 'non-empty cells' in 4 different sheets from A3 to last row then paste into sheet5. I think majority of code is doing what it is suppose to be doing except last one inside of my For ~ Next statement. As you can see Sheet1,2,3 and 4 gets filtered and results gets pasted into sheet5. It is suppose to paste the result then find the lastrow + 1 then paste and so on but It seems it is not doing that. Can anyone help? Thank you! Regards James Here is the part of the code Application.ScreenUpdating = False LastRange = Sheets("Sheet5").Range("A65000").End(xlUp).Row + 1 shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") With Sheets("Sheet5") Set CopyToRange = .Range("A" & LastRange) End With For sh = 0 To UBound(shArray) Sheets(shArray(sh)).Activate LRR = Range("A3").End(xlDown).Row Set FilterRange = Range("A3", Cells(LRR, "C")) FilterRange.AutoFilter field:=2, Criteria1:="<" FilterRange.Copy Destination:=CopyToRange FilterRange.AutoFilter ' Problem seems to start here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! With Sheets("Sheet5") Set CopyToRange = .Range("A3").End(xlDown) End With Next Application.ScreenUpdating = True |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I am pulling my hair out
On Jul 15, 5:59*pm, "Bob Phillips" wrote:
Not tested, but maybe * * Application.ScreenUpdating = False * * shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") * * For sh = 0 To UBound(shArray) * * * * With Sheets("Sheet5") * * * * * * LastRange = .Cells(.Rows.Count, "A6").End(xlUp)..Row + 1 * * * * * * Set CopyToRange = .Range("A" & LastRange) * * * * End With * * * * With shArray(sh) * * * * * * LRR = .Range("A3").End(xlDown).Row * * * * * * Set FilterRange = .Range("A3", .Cells(LRR, "C")) * * * * * * FilterRange.AutoFilter field:=2, Criteria1:="<" * * * * * * FilterRange.SpecialCells(xlCellTypeVisible).Copy Destination:=CopyToRange * * * * * * FilterRange.AutoFilter * * * * End With * * Next * * Application.ScreenUpdating = True -- __________________________________ HTH Bob "James8309" wrote in message ... Hi Everyone I have this code that filters 'non-empty cells' in 4 different sheets from A3 to last row then paste into sheet5. I think majority of code is doing what it is suppose to be doing except last one inside of my For ~ Next statement. As you can see Sheet1,2,3 and 4 gets filtered and results gets pasted into sheet5. It is suppose to paste the result then find the lastrow + 1 then paste and so on but It seems it is not doing that. Can anyone help? Thank you! Regards James Here is the part of the code Application.ScreenUpdating = False LastRange = Sheets("Sheet5").Range("A65000").End(xlUp).Row + 1 shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") * *With Sheets("Sheet5") * * * *Set CopyToRange = .Range("A" & LastRange) * * * * * *End With For sh = 0 To UBound(shArray) * *Sheets(shArray(sh)).Activate * * * *LRR = Range("A3").End(xlDown).Row * * * * * * *Set FilterRange = Range("A3", Cells(LRR, "C")) * * * * * * * *FilterRange.AutoFilter field:=2, Criteria1:="<" * * * * * *FilterRange.Copy Destination:=CopyToRange * * * *FilterRange.AutoFilter *' Problem seems to start here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! * *With Sheets("Sheet5") * * * * * * Set CopyToRange = .Range("A3").End(xlDown) * * * * * * * *End With Next Application.ScreenUpdating = True- Hide quoted text - - Show quoted text - I am getting object defined error everywhere (T_T) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I am pulling my hair out
Everywhere? For example?
-- __________________________________ HTH Bob "James8309" wrote in message ... On Jul 15, 5:59 pm, "Bob Phillips" wrote: Not tested, but maybe Application.ScreenUpdating = False shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") For sh = 0 To UBound(shArray) With Sheets("Sheet5") LastRange = .Cells(.Rows.Count, "A6").End(xlUp).Row + 1 Set CopyToRange = .Range("A" & LastRange) End With With shArray(sh) LRR = .Range("A3").End(xlDown).Row Set FilterRange = .Range("A3", .Cells(LRR, "C")) FilterRange.AutoFilter field:=2, Criteria1:="<" FilterRange.SpecialCells(xlCellTypeVisible).Copy Destination:=CopyToRange FilterRange.AutoFilter End With Next Application.ScreenUpdating = True -- __________________________________ HTH Bob "James8309" wrote in message ... Hi Everyone I have this code that filters 'non-empty cells' in 4 different sheets from A3 to last row then paste into sheet5. I think majority of code is doing what it is suppose to be doing except last one inside of my For ~ Next statement. As you can see Sheet1,2,3 and 4 gets filtered and results gets pasted into sheet5. It is suppose to paste the result then find the lastrow + 1 then paste and so on but It seems it is not doing that. Can anyone help? Thank you! Regards James Here is the part of the code Application.ScreenUpdating = False LastRange = Sheets("Sheet5").Range("A65000").End(xlUp).Row + 1 shArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") With Sheets("Sheet5") Set CopyToRange = .Range("A" & LastRange) End With For sh = 0 To UBound(shArray) Sheets(shArray(sh)).Activate LRR = Range("A3").End(xlDown).Row Set FilterRange = Range("A3", Cells(LRR, "C")) FilterRange.AutoFilter field:=2, Criteria1:="<" FilterRange.Copy Destination:=CopyToRange FilterRange.AutoFilter ' Problem seems to start here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! With Sheets("Sheet5") Set CopyToRange = .Range("A3").End(xlDown) End With Next Application.ScreenUpdating = True- Hide quoted text - - Show quoted text - I am getting object defined error everywhere (T_T) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pulling my hair out | Excel Programming | |||
| Pulling my hair out here | Excel Worksheet Functions | |||
| combo box problem I am pulling my hair out about!!!! | Excel Worksheet Functions | |||
| Pulling hair out with VLOOKUP | Excel Worksheet Functions | |||
| Hair Pulling Problem... | Excel Programming | |||
Linear Mode
