Select Cells and Copy Paste Selected Data

[fari1]

I want to have a code, that gives the output of just selected data. The example is below:

ColumnA
Row
1
2 Alpha
3
4 Beta
5
6 Gamma
7
8 Alpha
9
10 Beta
11
12 Alpha
13
14 Alpha
15
16 Gamma
17
18 Alpha
19
20 Beta


in the above example, in Column A there are multiple cells which contain some text and blank cells, i want the code to select, just those cells which contain Alpha and Beta and paste them in sheet2, as this is the dynamic range, there can be even no data, or row that contain just one cell of data or can be beyond 100 cells of data. any help over it would be greatly appreciated. Also i want the code to just select,copy and paste and not the delete empty or unwanted cells, as the other columns contain some valuable data in the sheet.

 

[villy]

Soemthing like this...

Sub selecttext()
lRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i) = "Alpha" Or Range("A" & i) = "Beta" Then
        Sheet2.Range("A" & lRow + 1).Value = Sheet1.Range("A" & i)
        lRow = lRow + 1
    End If
Next
End Sub

 

[Yahya]

try this too

Sub CopyAndPaste()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .AutoFilter 1, "Alpha", xlOr, "Beta"
    .Copy Sheets("sheet2").Range("a1")
End With

End Sub

 

[fari1]

this code is not giving results somehow, no error,no data

 

[fari1]

ok yahya, let me give it a try

 

[Yahya]

Fari
both codes work on this data

Excel Workbook
	A
1	Data
2	Alpha
3	Beta
4
5	Alpha
6	Beta
7	Beta
8	Alpha
9
10	Gamma
11
12	Gamma
13	Alpha
Sheet1

Excel 2007

HTH

 

[fari1]

Sub CopyAndPaste()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Sheets("info").Range("A1:A" & LR)
    .AutoFilter 1, "10-K", xlOr, "10-Q", xlOr, "10-Q/A", xlOr, "10-K/A"
    .copy Sheets("data").Range("a1")
End With
End Sub

this code is giving error on this line

.AutoFilter 1, "10-K", xlOr, "10-Q", xlOr, "10-Q/A", xlOr, "10-K/A"

wrong no.of arguments,or invalid use of property

 

[fari1]

what does this mean?

 

[fari1]

pl let me know just one thing, can i add more than two criterias, to find apart from alpha beta and gamma as well, will that be the same code, as i posted

 

[Yahya]

try this one

Sub CopyAndPaste()
Dim LR As Long
LR = Sheets("info").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("info").Range("A1:A" & LR)
    .AutoFilter 1, Criteria1:=Array("10-K", "10-K/A", "10-Q", "10-Q/A"), Operator:=xlFilterValues
    .Copy Sheets("data").Range("a1")
End With
End Sub