VBA HELP

[Closed]
Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
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 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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..