vba coding help

[Ron99]

Hi,

I have a spreadsheet say sheet1, under A1 I have a list of people's name as a drop down, have done a data validation on that.

scenario-1

when I select any name in the drop down, I get a data under A3:H18. whenever I select any of the name in the drop down, I need a formula or vba code to select the data below dynamically which is in (A3 :H18), please note every person might have different rows of data, once the data is selected I want that data to copy to the next sheet.

Scenario - 2

I want the VBA code to select everyones name in the drop down one after the other automatically and copy their data and paste it in the next sheet one below the other.


I tried recoring a macro, but unfortunately, when I select the names in the drop down, VBA code is not recorded rather its not identifying the names in the drop down. I am very new to vba. please help.

Regards,
Ron..

 

[Peter_SSs]

If we can do Scenario 2, does that mean you don't then need to do Scenatio 1 at all?

It seems that you have Data Validation in cell A2. Is the list of names for that DV on the sheet or in the workbook somewhere? Where?

 

[Ron99]

Hi,

Thanks for the quick reply, yes you are right, we can just go with the scenario 2, that would be fantastic.

Yes you are correct, DV is in A2 and its from the data sheet3.

Regards,
Ron..

 

[Peter_SSs]

Yes you are correct, DV is in A2 and its from the data sheet3.

Where, in sheet3 is the list of names? (For example the first name might be in A2 of sheet3 and downwards from there)

What is the name of the sheet that the data is to be copied to?

 

[Ron99]

Hi again,

The list of names are in a sheet named ( GSC activities), from F4:P4.

The validation is in the sheet name (worksheet )

The data which needs to be copied is under sheet name ( Summary)

Apologies for not giving details!

Regards,
Ron

 

[Peter_SSs]

I assume that the list on 'GSC activities' is actually a named range since you are using it in Data Validation on another sheet so just change the code to match your named rane.

Test in a copy of your workbook.

The code first clears anything (except row 1) that is already on the 'Summary' sheet. Not sure if that is what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> Cel <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Summary")<br>        .UsedRange.Offset(1).ClearContents<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> Range("myNames") <SPAN style="color:#007F00">'<--change to your named range</SPAN><br>            Sheets("worksheet").Range("A2").Value = Cel.Value<br>            .Range("A" & .Rows.Count).End(xlUp) _<br>                .Offset(1).Resize(16, 8).Value _<br>                    = Sheets("worksheet").Range("A3:H18").Value<br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Ron99]

Thank you,

The code extracted, the name which was in the worksheet, and pasted perfectly in the sheet named summary,

now my question is, it just copied the data for one name, the remaning data is not copied.

so under this code:

For Each Cel In Range("My Names") '<--change to your named range

My names, means the range which is in GSC activity list, which is F4 :P4 ?

Sorry I am dumb with VBA.

 

[Peter_SSs]

If you select cell A2 in sheet 'worksheet' and go to Data Validation, what appears on the 'Settings' tab in the 'Allow' box and any subsequent boxes?

 

[Ron99]

Hi,

Since the validation was from another sheet ( GSC activity), I had used name manager, and named the range as "NameList". when I did the validation in "worksheet' tab

I replaced this function with "NameList"

For Each Cel In Range("NameList") '<--change to your named range

After running the code,

What has happened is the data is copied from the first name only in the summary sheet. It hasnt extracted all names.

instead it has copied 10 times the same data, coz there where 10 names in that list.

 

[Peter_SSs]

I have assumed that cells A3:H18 on 'worksheet' contain formulas so that when cell A2 on that sheet changes, so do the values in A3:H18. Is that correct?