Creating a new worksheet from another
 

Hi

I have a little problem, any help would be GREAT!

Heres what Im trying to do,

I have a worksheet, column A has company names, and column B either ha
an 'S' or an 'L'.


Now in another worksheet, I want to print all the company names i
column A that have an "L" in column B, starting at row 4.

This is what I have:

rivate Sub Large_Click()
Dim i As Long
Dim j As Long
Dim company As String

Sheet2.Activate
Sheet2.Range("A4").Select

Count = 0
For j = 1 To Sheet2.Range("A65536").End(xlUp).Row
If Sheet2.Range("B" & j).Value = "L" Then
Count = Count + 1
End If
Next

For i = 1 To Count
Sheet2.Activate
For j = 1 To Sheet2.Range("A65536").End(xlUp).Row
If Sheet2.Range("B" & j).Value = "L" Then
company = Sheet2.Range("A" & j).Value
Sheet4.Activate
Sheet4.Range("A" & i + 3).Value = company
End If
Next j
Next i

Sheet4.Activate

End Sub

Problems with this:

1. it only prints the last of the three companies, three times
2. If I add an 'Exit For' for btoh loops, then it only prints the firs
such comapny, once
3. If I only add an 'Exit For' for the inner loop, it prints the firs
of the three companies, three times
4. If I only add an 'Exit For' for the outer loop, it only prints th
last of the three companies, once

Any help or ideas??

--
Message posted from http://www.ExcelForum.com

Creating a new worksheet from another
 

This is untested, but I think it will do the trick:


Code
-------------------
Sub test()

Dim i As Long
Dim iRow As Long

Sheets("Sheet2").Select
iRow = 4
For i = 4 To Range("A65536").End(xlUp).Row
If Range("B" & i).Value = "L" Then
Sheet4.Range("A" & iRow).Value = Range("A" & i).Value
iRow = iRow + 1
End If
Next

End Su
-------------------



--
Message posted from http://www.ExcelForum.com

Creating a new worksheet from another
 

Thank you so much!!!!!!!!

Sheen

--
Message posted from http://www.ExcelForum.com