help with VBA codes to extract selected data from worksheet on specific conditions

[HarryC]

Hello to all

I am new to this forum so first of all "A very warm hello" and I really really apologise if i am duplicating this OR posting this into a wrong thread..

Now, i have attached a datasheet which has got lots of column. (see Sheet1)

Want i want to achieve is, I have got a button on sheet2 called "Sue Hendry". I want my users to click on the button and wherever (on sheet1), Sue Hendry on column Y has got "Y", i want it to retrieve all the data for that. which is from column A to column S

Is this possible?

Eventually , i want to have a button for each heading from column T to column AC and it needs to do the exact same thing.

PLease, any help be appreciated

[ragulduy]

something along the lines of:

sub sue_hendry()
dim count, count_1
count_1 = 1
for count = 2 to 7
 if Sheets("Sheet1").Range("Y" & count) = "Y" then
   Sheets("Sheet2").Range("A" & count_1 & ":S" & count_1).value=Sheets("Sheet1").Range("A" & count & ":S" & count).value
 end sub
next
end sub

[HaHoBe]

Hi, HarryC,

welcome to ExcelForum.

Just take a second to reflect on your thread title and image you yourself would do a search on that title: what would you expect as a result? Maybe take your time and change it according to the forum rules to something a bit more reflecting your problem, and I´m sure we will be able to help you with your problem.

Ciao,
Holger

[HarryC]

Thanks so much Holger and appreciate your prompt response.
and i apologise for the title and being naive about my forum title

Coming back to the solution, the above didnt work
I replaced the
end sub
next
end sub

WITH
end IF
next
end sub

It worked although, in the Sheet1, it has 3 places where column Y (sue Hendry) got "Y". but it only returns 1 row.

And is there any way, the data is retrieved in a new sheet rather than top of the same sheet and it also displays the result with the column headers? please..thanks again guys..Really appreciate it

To enable you guys to help me further, i have attached a new sheet with the above macro

[ragulduy]

Apologies, I should have tested that better, try this:

Sub sue_hendry()
Dim count, count_1
count_1 = 1
For count = 2 To 7
 If Sheets("Sheet1").Range("Y" & count) = "Y" Then
   Sheets("Sheet3").Range("A" & count_1 & ":S" & count_1).Value = Sheets("Sheet1").Range("A" & count & ":S" & count).Value
   count_1 = count_1 + 1
 End If
Next
End Sub

Change Sheet3 to the name of the sheet you want to put the data in, alternatively to add a sheet and put it in there:

Sub sue_hendry()
Dim count, count_1,ws
set ws = sheets.add
count_1 = 1
For count = 2 To 7
 If Sheets("Sheet1").Range("Y" & count) = "Y" Then
   ws.Range("A" & count_1 & ":S" & count_1).Value = Sheets("Sheet1").Range("A" & count & ":S" & count).Value
   count_1 = count_1 + 1
 End If
Next
End Sub

[HarryC]

This is brilliant

Is there any way i could have the data displayed with the column headers please....
This should be it for me to resolve my query :D

[ragulduy]

Change

count_1 = 1

to

count_2 = 1

and copy the information across using either:

Sheets("Sheet1").rows(1).copy Sheets("Sheet3").Range("A1")

or

Sheets("Sheet1").rows(1).copy ws.Range("A1")

Depending on whether you are using an existing sheet or adding a new one.

[HarryC]

@yudlugar

Hello there

Thanks a lot for helping me out

One last query please. Probably being thick here. How can i allow the second macro to retrieve the data for Dawn Gray for example. I have given the thought of retrieving the data in the same sheet. If you see on Sheet 2, I have got the data retrieved for Sue Hendry. What i would like to do now is click on "Dawn Gray" and it clears the data for Sue and retrieves the data for Dawn?

Is this possible. For example. i am attaching the latest sheet.

Thanks

[ragulduy]

Sorry, not sure if I'm allowed to help or not.

[HaHoBe]

Hi, yudlugar,

green light is on for you to help

jeffreybrown has requested a change of title, HarryC has responded to that and altered the thread title, so jeffrey deleted his post as he had stated in it because their no longer was a need for it.

Ciao,
Holger

[HarryC]

please help me. I think administrator is happy now as i have changed my title

[ragulduy]

Ok, wasn't sure because some posts still have the old title.

HarryC,

This:

 If Sheets("Sheet1").Range("Y" & count) = "Y" Then

Is the line that selects Sue Hendry (column Y), so for Dawn Grey you would need to change it to:

 If Sheets("Sheet1").Range("Z" & count) = "Y" Then

to clear the data:

Sheets("Sheet2").cells.clearcontents

[HaHoBe]

Hi, yudlugar,

because some posts still have the old title.

From what I remember the posts are written into the database with the thread title that was relevant the moment they were posted keeping the identying number of the starting post. The database will update the thread title accordingly to keep the record set together but the change of thread title may only be achieved by Admins/Mods changing them manually. Any new post will feature the altered or actual thread title.

This procedure should be the same on all vBulletin boards.

Ciao,
Holger

[HarryC]

thanks again Yudlugar and exactly where this
Sheets("Sheet2").cells.clearcontents
Needs to be placed?

[ragulduy]

At the start,

e.g.

Sub sue_hendry()
Dim count, count_1, count_2
Sheets("Sheet2").cells.clearcontents

count_1 = 1
Sheets("Sheet1").Rows(1).Copy Sheet2.Range("A1")
count_2 = 2
For count = 2 To 7
 If Sheets("Sheet1").Range("Y" & count) = "Y" Then
   Sheets("Sheet2").Range("A" & count_2 & ":S" & count_2).Value = Sheets("Sheet1").Range("A" & count & ":S" & count).Value
   count_2 = count_2 + 1
 End If
Next
End Sub
Sub dawn_gray()
Dim count, count_1, count_2
Sheets("Sheet2").cells.clearcontents
count_1 = 1
Sheets("Sheet1").Rows(1).Copy Sheet2.Range("A1")
count_2 = 2
For count = 2 To 7
If Sheets("Sheet1").Range("Z" & count) = "Y" Then
   Sheets("Sheet2").Range("A" & count_2 & ":S" & count_2).Value = Sheets("Sheet1").Range("A" & count & ":S" & count).Value
   count_2 = count_2 + 1
 End If
Next

[HarryC]

Thanks Yudlugar. I appreciate your help. I will give this a go and see if it works. I am sure it will. Will come back later