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.

 

[fari1]

oh wow, it created a filter, this code is to create a filter?, i need the plain and simple data, and just those values from column A, how would i use this sheet to copy and paste just my required data? also this code is a part of loop, when next data would come in, would it easily replace this filtr or not?

 

[fari1]

i guess now just one line needs to be added into it, to copy my 10-k,10-Q,10-K/A,10-Q/A and paste them into final filtered sheet, its because i dun need the rest of the sheet

 

[villy]

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

Try it again this should work as you required.

 

[fari1]

this is highlighting this part of the code

rng = Sheets("info").Range("A" & i)

 

[villy]

this is highlighting this part of the code

rng = Sheets("info").Range("A" & i)

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

I'm sorry my bad this should work now..

 

[fari1]

Hi Villy,
pl tell me your email id, let me send u the file, it would be easy for u to check and test it

 

[Yahya]

fari does my code work with you or not

 

[fari1]

i've sent u email, pl check it

 

[fari1]

yahya, your code is creating autofilter, which i dun want, the whole conatins data and autofilter is disturbing the other codes, also, web query is bringin in data in that sheet, autofilter is not what i want

 

[villy]

This should work...

Sub CopyAndPaste()
'10-K,10-K/A,10-Q,10-Q/A
lRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Rng = Sheets("Sheet1").Range("A" & i)
    Select Case Rng
        Case "10-K", "10-K/A", "10-Q", "10-Q/A"
            Sheet2.Range("A" & lRow + 1).Value = Rng
            lRow = lRow + 1
    End Select
Next
End Sub

Sorry I cannot send you the file because I am using 2003 and cannot convert to 2007 just use this code it works now as you required...