Extract data from one worksheet and put in another where cell = *

[bluegnu]

I've done a bit of a search on the forums and can't find or don't understand what I'm looking for, so apologies if this is already in available.

I have a worksheet (excel 2003) which is a basic table. For example:

Number  Description    Comment    Tag
1.0     Something      Blah        *
2.0     Else           Blah Blah
3.0     Sheep          Wolf        *

In the above example I would like to copy Columns B(Description) and C (Comment) where Column D (Tag) = * and paste it into a new worksheet.

How can I do this? Also, how would I paste it into a different worksheet?

many thanks

 

[xlbo]

filter on your "tag" column and copy all rows to the new spreadsheet - the copy/paste will take only the visible rows so only those with a * will be taken across

Record yourself doing this with the macro recorder as a 1st step....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluegnu]

Thanks for the reply. This works fine in the example I gave, but in reality I'd hope to select columns which aren't next to each other and on a much larger scale. I'm hoping its possible to do this with a VBA macro to speed things up!

 

[xlbo]

Please be more specific with your questions, stating exactly what your requirement is from the outset - the quality of answer you get will be directly proportional to the quality of question !

Your start point is still the same - filter for * in yout "tag" column - then rather than copying the whole block of data - do it a column at a time

I was already suggesting that this is done in a macro - that is why I suggested you record the actions you take manually as a start point...

Tools>Macro>Record New MAcro

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluegnu]

Apologies, I will try and make it clearer. I'm really trying to work out what I want as well as how to do it. So:

Sheet 1 lookes like this:

  A          B            C           D         E
Number  Description    Comment      Person     Tag
1.0     Something      Blah          Dave       *
2.0     Else           Blah Blah     Jack
3.0     Sheep          Wolf          Steve      *

Sheet 2 lookes like this:

  A          B            C           D         E
Number  Description    Comment      Person     Tag
1.0     Apple          Peel          Dave       *
2.0     Banana         Skin          Jack
3.0     Orange         Pith          Steve

At the moment, what I would like to do is create a macro that will extract everything in columns B and D where the Tag has a * into a new worksheet and also into a new workbook.

My new sheet would look like this:

  A              B        
Description    Person
Something       Dave
Sheep           Steve
Apple           Dave

I really don't know what to do in Excel other than how to look at VB Editor and really basic stuff.

An example of the code I might use in this instance would be good so I can learn from it an manipulate it.

Many thanks

 

[PHV]

An example of the code I might use in this instance would be good so I can learn from it an manipulate it
As previously stated, the macro recorder is your friend.