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]

in your code, i get confused, from which sheet it is copying and in which sheet it is pasting, as i have different sheet names in my actual file, so getting confused. on my side, the sheet from which it must copy data is having the name info and sheet in which it'll paste has the name data. will u pl amend the code, as i'm unable to find out

 

[fari1]

its not working villy......... helppp

 

[villy]

in your code, i get confused, from which sheet it is copying and in which sheet it is pasting, as i have different sheet names in my actual file, so getting confused. on my side, the sheet from which it must copy data is having the name info and sheet in which it'll paste has the name data. will u pl amend the code, as i'm unable to find out

The one you sent me should give you the answer on which sheet it is to be run..
You gave me sheet1 and sheet2 only so I use these sheets.. Change it to suit your sheets name

 

[villy]

in your code, i get confused, from which sheet it is copying and in which sheet it is pasting, as i have different sheet names in my actual file, so getting confused. on my side, the sheet from which it must copy data is having the name info and sheet in which it'll paste has the name data. will u pl amend the code, as i'm unable to find out

change sheet1 to info and sheet2 into data respectively

 

[fari1]

lRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

in that workbook which i gave u, its highlighting above line of code

 

[fari1]

i've changed the names, but they'r not working, give me that 2003 file, i'll change it, i've a converter

 

[villy]

lRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

in that workbook which i gave u, its highlighting above line of code

You have to change sheet2 to data

 

[fari1]

its not even working in the sheet, which i gave u, and even not when i change its name

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

 

[villy]

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

Hope it is doing fine now...

 

[villy]

you should run your macro in sheet("info")
or better yet
in sheets("info")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call CopyAndPaste
End Sub