This macro should do it:
Option Explicit
Sub CopyData()
Dim dRow
Dim nRows, R
Dim ShtSource, ShtDest
ShtSource = "Sheet1"
ShtDest = "Sheet2"
Sheets(ShtSource).Select
nRows = ActiveCell.SpecialCells(xlLastCell).Row
dRow = 1
'----------------------
Sheets(ShtDest).Range("A2:Z65000").ClearContents
For R = 1 To nRows
If ((Sheets(ShtSource).Cells(R, "J").Value = "Apple") _
Or (Sheets(ShtSource).Cells(R, "J").Value = "Pear")) Then
dRow = dRow + 1
Sheets(ShtDest).Range("A" & dRow & ":E" & dRow).Value = _
Sheets(ShtSource).Range("F" & R & ":J" & R).Value
End If
Next R
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: Paul Schreiner <schreiner_p...@att.net>
To: excel-macros@googlegroups.com
Sent: Tue, December 18, 2012 8:19:27 AM
Subject: Re: $$Excel-Macros$$ Re: Excel macro help please
NOW I think I understand!
See... by saying:
that you're searching in column J, and when you encounter "Apple" or "Pear",
then you copy the previous (4) cells,
the term "previous" implies the cells searched previously.
The term you want is ADJACENT, which would mean the cells "next to"
the matching
cell, in the same row.
NOW it makes more sense.
Am I to assume that sheet2 is to be empty at the time the macro starts?
(I can have the macro clear it)
I'll throw something together and see what you think.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: Stuart Wilson <swilson2...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Tue, December 18, 2012 7:52:37 AM
Subject: Re: $$Excel-Macros$$ Re: Excel macro help please
Paul
Thanks for your message.
So the exact steps I would like are as follows:
1. Data is all in sheet1 in rows.
2. Macro search down column J and look for the word "Apple" or "Pear" and when
it finds those words select the cell containing the word plus the previous 4
cells and copy them all.
3. GO to sheet 2 and paste them in the first available row in that worksheet.
3. End macro
I don't need a button just the code.
I thought it was relatively clear what I wanted so sorry I've confused you.
On 18 December 2012 12:32, Paul Schreiner <schreiner_p...@att.net> wrote:
I'm not trying to be mean (honestly)
>but you need to work on your "posting skills".
>As the forum guidelines indicate:
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>
>Nearly EVERYONE that posts to this group would like help with a macro.
>What YOU would like to do is find some people that can help with your specific
>problem.
>Your title helps to catch the eye of the people with the expertise (and
>interest) in your problem.
>
>Secondly,
>Your description is confusing.
>"If cell value in column J of worksheet "Sheet1" = "Apple" or "Pear" then copy
>that cell plus the previous 4 cells to a new worksheet called "Sheet2".
>
>Are you wanting to run the macro as a button? or when a value is entered?
>In your sample file, I would have expected to find (5) cells filled
in "Sheet2".
>(since, I would assume, "Apple" or "Pear" was going to be entered into a cell
>in
>Sheet1)
>Instead, I see (7) ROWS of data (not cells)
>
>Could you please explain the STEPS in which you wish to activate this macro.
>What the sheets look like to START.
>What you plan to do to make the macro run.
>what is to be "tested" and what EXACTLY you expect to see happen?
>
>Paul
>-----------------------------------------
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-----------------------------------------
>
>
>
>
>
________________________________
From: sw1085 <swilson2...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Tue, December 18, 2012 3:23:00 AM
>Subject: $$Excel-Macros$$ Re: Excel macro help please
>
>Please see attached file with a sample of what I would expect to see in sheet2
>
>
>Thanks, hope you can help.
>
>
>On Monday, December 17, 2012 4:27:21 PM UTC, sw1085 wrote:
>Hi all,
>>
>>
>>I've been searching for hours looking for a generic macro to help with the
>>following problem.
>>
>>
>>If cell value in column J of worksheet "Sheet1" = "Apple" or "Pear" then copy
>>that cell plus the previous 4 cells to a new worksheet called "Sheet2".
>>
>>
>>Hope someone can help.
>>
>>
>>Best regards
>>
>>
>>sw1085--
>Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members
>are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To post to this group, send email to excel-macros@googlegroups.com.
>To unsubscribe from this group, send email to
>excel-macros+unsubscr...@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
>
>--
>Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members
>are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To post to this group, send email to excel-macros@googlegroups.com.
>To unsubscribe from this group, send email to
>excel-macros+unsubscr...@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
>
>
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
