VBA HELP

Closed
ify - Jun 2, 2009 at 09:07 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Jun 3, 2009 at 05:05 AM
Hello,
I am trying to copy a number of rows to another sheet. For example, I use a code to test the second column for <= 30. Then I want excel to copy the rows of these cells that are less than 32 to new worksheet that i also want the code to create. So far, my code only takes one row that is less tahn 32 and pastes it in a new sheet. I need it togo thorugh each row and paste all rows with (<=32) into the new sheet. Any help is highly appreciated. Here is the code i have so far:
1 33
2 7
3 8
3 21
4 22
5 43
6 71
7 25
8 15
9 16
0 19

Sub Crop3()
'try icounter
Sheets("Sheet3").Activate

Dim intcounter As Range
For Each intcounter In Selection
'For Each intcounter In Worksheets("Sheet3").Range("F4:F14").Cells

If intcounter <= 32 Then

intcounter.EntireRow.Select


'Application.CutCopyMode = False
Selection.Copy

End If
Next
'Make a new worksheet
Set work_book = Application.ActiveWorkbook
Set last_sheet = _
work_book.Sheets(work_book.Sheets.Count)
Set new_sheet = work_book.Sheets.Add(after:=last_sheet)
new_sheet.Name = "Buildup"
Sheets("Buildup").Activate
Range("E2:F2").EntireRow.Select
ActiveSheet.Paste


End Sub

1 response

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 3, 2009 at 05:05 AM
Hi

Make the new sheet before the FOR..NEXT loop
in the loop when the condition is satisfied at a row, select the sheet and paste and return to original sheet

Hope this helps..
0