VBA CODE to copy and paste selected data

fari1

fari1

Active Member
Joined
May 29, 2011
Messages
362
I've a range in column A,

Apple

Orange

Grapes

Apple

Orange

Banana


i want to just copy apple and orange out of this coloumn, ignoring blank and rest unwanted rows, just the cells with apple and orange and paste it into sheet2 columnA
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
If you use a custom autofilter to only show rows where this column is either 'apple' or 'orange', you can copy the filtered range and then paste to another sheet. If you need vb to do this, you could always record a macro whilst you do it.

HTH
 
Upvote 0
hey pl i need a code for it, if u could do something about it, would be greatful, its a part of the loop, can't have any manual interfernce
 
Upvote 0
fari1 said:
I've a range in column A,

Apple

Orange

Grapes

Apple

Orange

Banana


i want to just copy apple and orange out of this coloumn, ignoring blank and rest unwanted rows, just the cells with apple and orange and paste it into sheet2 columnA
Click to expand...

Does this help?

Code: 
Sub Fari1()
Dim rng As Range

For Each rng In Range(Range("A1"), Range("A" & Rows.Count))

    If rng.Value = "Apple" Or rng.Value = "Orange" Then
    
        rng.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
    
    End If

Next

End Sub
 
Upvote 0
fari1 said:
its not working, just processing and giving no results
Click to expand...

Based on your other thread

http://www.mrexcel.com/forum/showthread.php?p=2811625#post2811625

Try this change:

Code: 
Sub Fari1()
Dim rng As Range

Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart


For Each rng In Range(Range("A1"), Range("A" & Rows.Count))


    If rng.Value = "Apple" Or rng.Value = "Orange" Then
    
        rng.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
    
    End If

Next

End Sub
 
Upvote 0
<b>info</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:65px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >abc</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >SIC:7370-SERVICES-COMPUTERPROGRAMMING,DATAPROCESSING,ETC.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Statelocation:CA|StateofInc.:DE|FiscalYearEnd:1231</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >(AssistantDirectorOfficeNo3)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Getinsidertransactionsforthisissuer.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Getinsidertransactionsforthisreportingowner.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >FilterResults:</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >Items1-350001288776FilingsRSSFeed</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Filings</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td >10-K</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


my actual data looks like this, need to have 10-K,10-Q,10-K/A,10-Q/A values out of this range, all the values, not the unique ones, want to copy every possible repition of it and copy in sheet3, and also your code gets into some sort of processing, means takes time to execute and giving no results, perhaps the reason is my data structure. pl guide
 
Upvote 0
fari1 said:
<b>info</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:65px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >abc</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >SIC:7370-SERVICES-COMPUTERPROGRAMMING,DATAPROCESSING,ETC.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Statelocation:CA|StateofInc.:DE|FiscalYearEnd:1231</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >(AssistantDirectorOfficeNo3)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Getinsidertransactionsforthisissuer.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Getinsidertransactionsforthisreportingowner.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >FilterResults:</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >Items1-350001288776FilingsRSSFeed</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Filings</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td >10-K</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


my actual data looks like this, need to have 10-K,10-Q,10-K/A,10-Q/A values out of this range, all the values, not the unique ones, want to copy every possible repition of it and copy in sheet3, and also your code gets into some sort of processing, means takes time to execute and giving no results, perhaps the reason is my data structure. pl guide
Click to expand...

Modifying the Or statements in the code should give you what you want. The other solutions to your other thread should also produce results. There's evidently something wrong with your data. Try trimming Column A. and then make the changes and run the code again.
 
Upvote 0
Here's how you can use filtering to get what you want

Code: 
Sub Macro1()
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A13:A" & lr)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="=10*", Operator:=xlAnd
    End With
    Rows("14:" & lr).Copy Sheets("Sheet3").Range("A1")
    Cells.AutoFilter
End Sub
1. make sure there's a sheet called 'sheet3'
2. activate the tab with the original data
3. run the macro
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list
2 3
Replies
21
Views
1K
dmt32
D
P
  • Question Question
FILTER function with multiple criteria already in Include syntax
Replies
3
Views
545
Fluff
Fluff
S
  • Question Question
Help searching for strings in 2D array and returning multiple results
Replies
8
Views
653
Fluff
Fluff
S
  • Solved
Delete Row if value in column C is a duplicate
Replies
8
Views
279
igold
igold
R
  • Solved
Excel VBA to filter by unique value, copy paste into new workbook and save
Replies
2
Views
860
rittersportyummy
R

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top